Suppose we have simple Data Modal as follows.
Returning the Max Sale Date, and the Product that contributing the Max Sales
Net Sales = SUM(ds_Sample[Qnty_Sold])
Max Sales By Date =
MAXX(
VALUES(ds_Sample[Order_Date]),
[Net Sales]
/* CALCULATE(SUM(ds_Sample[Qnty_Sold])) */
)
Max Sale Date =
CALCULATE( FIRSTNONBLANK(ds_Sample[Order_Date],1),
FILTER( VALUES(ds_Sample[Order_Date]),
[Net Sales] = MAX(ds_Sample[Qnty_Sold])
)
)
Max Sale Prod By Date =
CALCULATE( FIRSTNONBLANK(ds_Sample[Prod_Name],1),
FILTER( VALUES(ds_Sample[Order_Date]),
[Net Sales] = MAX(ds_Sample[Qnty_Sold])
)
)
Result:
Note:
The Total value 174 is the Max Total Sales by Date not the Total of 3 Max Sales.
The Total value 174 is the Max Total Sales by Date not the Total of 3 Max Sales.
Scenario 2:
Returning the Unique Orders and Max Order value by Segment based on Order Date, with a Correct Total.
Max Sales = MAX(ds_Sample[Qnty_Sold])
Please note that, I have considered the [Order_Date] for the Unique Orders calculation, only to explain this Scenario.
The [Order_Date] Column is having Duplicate Records, as we can expect the multiple Orders on the same date.
Now, to fulfil this Scenario, we will fix the Total Issue for [Max Sales] and [Unique Orders] by using the Measures as shown below:
Max Sales (Total) =
SUMX( VALUES(ds_Sample[Prod_Segment]),
[Max Sales]
/* CALCULATE(MAX(ds_Sample[Qnty_Sold])) */
)
Unique Orders (Total) =
SUMX( VALUES(ds_Sample[Prod_Segment]),
[Unique Orders]
/* CALCULATE(DISTINCTCOUNT(ds_Sample[Order_Date])) */
)
Result:
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.