Forum Discussion

VoijaRisa's avatar
VoijaRisa
Frequent Visitor
2 years ago
Solved

TOPN SUMMARIZE with SWITCH measures

I'm attempting to understand why a measure I've written doesn't work and see if there's something I can do to fix it.   Overview of Data & Measures I'm dealing with shipping data and have a Table ...
  • Anonymous's avatar
    Anonymous
    1 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.