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,
I am not sure of how much i can help but i would like to try. Share the download link of the PBI file.