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...
Need Help
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
)