Various totals in Matrix/table visual

[Haychman]

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

Category	Description	Code	Calculation
3rd party	top 10	W
3rd party	Other	X	X=(Y-W)
3rd party	Total	Y
Intercompany	Total	A
Grand total		B	B=(Y+A)

[Sahir_Maharaj]

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
)