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
--------------------------------------------------------------------------------------------------------