Forum Discussion

Haychman's avatar
Haychman
New Member
1 year ago

Various totals in Matrix/table visual

Hi All,

 

I hope everyone is doing well.

 

I have a table which displays the clients and their amounts. I have a seperate table below which calculates (through measures) the total value of top 10 clients,  the total value of clients outside of the top 10, the total value of intercompany clients and 3rd party clients and then the total value of debt.

 

What i'm looking to do is combine the totals in the first table. I would like the table to display (for 3rd party only) the top 10 items by amount, the total outside of the top 10(again for 3rd party only), the total 3rd party, total Intercompany (i.e. items which are not 3rd party) and finally the total of everything in debt.

 

i've written a formula just to make it easier to understand

 

 

 

CategoryDescriptionCodeCalculation
3rd partytop 10W 
3rd partyOtherXX=(Y-W)
3rd partyTotalY 
IntercompanyTotalA 
Grand total BB=(Y+A)

 

 

Need Help

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    1 year ago

    Hello Haychman,

     

    Can you please try this approach:

    Combined KPI = 
VAR Top10_3rdParty =
    CALCULATE(
        SUM('Debt'[Amount]),
        FILTER(
            'Debt',
            'Debt'[Category] = "3rd Party"
        ),
        TOPN(10, 'Debt', 'Debt'[Amount])
    )
VAR Other_3rdParty =
    CALCULATE(
        SUM('Debt'[Amount]),
        FILTER(
            'Debt',
            'Debt'[Category] = "3rd Party"
        )
    ) - Top10_3rdParty
VAR Total_3rdParty =
    CALCULATE(
        SUM('Debt'[Amount]),
        FILTER(
            'Debt',
            'Debt'[Category] = "3rd Party"
        )
    )
VAR Total_Intercompany =
    CALCULATE(
        SUM('Debt'[Amount]),
        FILTER(
            'Debt',
            'Debt'[Category] = "Intercompany"
        )
    )
VAR Grand_Total =
    Total_3rdParty + Total_Intercompany

RETURN
SWITCH(
    TRUE(),
    ISINSCOPE('Debt'[Category]) && 'Debt'[Category] = "3rd Party" && ISINSCOPE('Debt'[Subcategory]) && 'Debt'[Subcategory] = "Top 10", Top10_3rdParty,
    ISINSCOPE('Debt'[Category]) && 'Debt'[Category] = "3rd Party" && ISINSCOPE('Debt'[Subcategory]) && 'Debt'[Subcategory] = "Other", Other_3rdParty,
    ISINSCOPE('Debt'[Category]) && 'Debt'[Category] = "3rd Party", Total_3rdParty,
    ISINSCOPE('Debt'[Category]) && 'Debt'[Category] = "Intercompany", Total_Intercompany,
    Grand_Total
)