Forum Discussion
TOPN SUMMARIZE with SWITCH measures
- Anonymous1 year ago
Hi VoijaRisa
Based on your detailed description, I have added some steps to the actions you have already taken, and in my simple tests it is working. I hope the following steps will be helpful to you.
Here's my data model:
1. Create the "Total Amount Paid (USD) Sum" measure in the same way as you used it.
2. Create separate measures to calculate Top5 for different date selections.
Top 5 Ship Date = SUMX( TOPN( 5, ADDCOLUMNS( SUMMARIZE( 'Shipments', 'Shipments'[Origin], 'Shipments'[Destination] ), "Total Paid USD", [Total Amount Paid (USD) Sum] ), [Total Paid USD], DESC ), [Total Paid USD] )Top 5 Invoice Date = CALCULATE( SUMX( TOPN( 5, ADDCOLUMNS( SUMMARIZE( 'Shipments', 'Shipments'[Origin], 'Shipments'[Destination] ), "Total Paid USD", [Total Amount Paid (USD) Sum] ), [Total Paid USD], DESC ), [Total Paid USD] ), USERELATIONSHIP('Date'[DATE], 'Shipments'[INVOICE_DATE]) )Top 5 Processed Date = CALCULATE( SUMX( TOPN( 5, ADDCOLUMNS( SUMMARIZE( 'Shipments', 'Shipments'[Origin], 'Shipments'[Destination] ), "Total Paid USD", [Total Amount Paid (USD) Sum] ), [Total Paid USD], DESC ), [Total Paid USD] ), USERELATIONSHIP('Date'[DATE], 'Shipments'[PROCESSED_DATE]) )3. Combine the Top5 measures created above into one measure.
Top 5 = VAR DateSelection = SELECTEDVALUE('Date Slicer Choices'[Date Choice]) RETURN SWITCH( TRUE(), DateSelection = "Invoice Date", [Top 5 Invoice Date], DateSelection = "Processed Date", [Top 5 Processed Date], DateSelection = "Ship Date", [Top 5 Ship Date] )4. Create a card visual with Top 5 measure.
5. Here is final result:
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi VoijaRisa
Based on your detailed description, I have added some steps to the actions you have already taken, and in my simple tests it is working. I hope the following steps will be helpful to you.
Here's my data model:
1. Create the "Total Amount Paid (USD) Sum" measure in the same way as you used it.
2. Create separate measures to calculate Top5 for different date selections.
Top 5 Ship Date =
SUMX(
TOPN(
5,
ADDCOLUMNS(
SUMMARIZE(
'Shipments',
'Shipments'[Origin],
'Shipments'[Destination]
),
"Total Paid USD", [Total Amount Paid (USD) Sum]
),
[Total Paid USD], DESC
),
[Total Paid USD]
)
Top 5 Invoice Date =
CALCULATE(
SUMX(
TOPN(
5,
ADDCOLUMNS(
SUMMARIZE(
'Shipments',
'Shipments'[Origin],
'Shipments'[Destination]
),
"Total Paid USD", [Total Amount Paid (USD) Sum]
),
[Total Paid USD], DESC
),
[Total Paid USD]
),
USERELATIONSHIP('Date'[DATE], 'Shipments'[INVOICE_DATE])
)
Top 5 Processed Date =
CALCULATE(
SUMX(
TOPN(
5,
ADDCOLUMNS(
SUMMARIZE(
'Shipments',
'Shipments'[Origin],
'Shipments'[Destination]
),
"Total Paid USD", [Total Amount Paid (USD) Sum]
),
[Total Paid USD], DESC
),
[Total Paid USD]
),
USERELATIONSHIP('Date'[DATE], 'Shipments'[PROCESSED_DATE])
)
3. Combine the Top5 measures created above into one measure.
Top 5 =
VAR DateSelection = SELECTEDVALUE('Date Slicer Choices'[Date Choice])
RETURN
SWITCH(
TRUE(),
DateSelection = "Invoice Date", [Top 5 Invoice Date],
DateSelection = "Processed Date", [Top 5 Processed Date],
DateSelection = "Ship Date", [Top 5 Ship Date]
)
4. Create a card visual with Top 5 measure.
5. Here is final result:
Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.