contingency table with calculated rows

Question

Hello,&nbsp;&nbsp;I have the data source as you can see on the picture on the left side (you can find it also in the table inserted below. My goal is to have the contingency table - the same like you can see on the green background on the picture.&nbsp;&nbsp;My questions to this problem are:1) How to create the contingency table with rows made up of "ID"2) How to calculate the values EBITDA, EBITDA (%), EBIT and EBIT (%)3) How to have all these rows in just one contingenc table?&nbsp;&nbsp;DateIDAmountType20.04.202313010300EBITDA10.05.202313011800EBITDA13.04.20231308900EBITDA10.05.2023210-5400EBITDA01.04.2023210-6500EBITDA13.04.2023210-4900EBITDA24.05.2023210-300EBITDA10.05.2023550-2500EBIT24.05.2023550-2300EBIT13.04.2023550-1900EBIT&nbsp;Thank you in advance for your suggestion of solution. It will help a lot 🙂&nbsp;František&nbsp;

anonymous · Accepted Answer

Hi,You can use the following DAX to create a calculation table so that all rows are in the same column.
Contingency table = 
VAR _tb1 = FILTER('Table','Table'[ID]IN{130,210}) 
VAR _tb2 = FILTER('Table','Table'[ID] = 550) 
RETURN
UNION(
    SELECTCOLUMNS('Table',"ID",'Table'[ID],"Month",'Table'[Year-Month],"Amount",'Table'[Amount]), 
   DISTINCT(
        SELECTCOLUMNS(
            _tb1,"ID","EBITDA","Month",'Table'[Year-Month],"Amount",CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Year-Month]),KEEPFILTERS('Table'[ID] IN {130,210})))
        ), 
    DISTINCT(
        SELECTCOLUMNS(
            _tb1,"ID","EBITDA(%)","Month",'Table'[Year-Month],"Amount",
            DIVIDE(
                CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Year-Month]),KEEPFILTERS('Table'[ID] IN {130,210})), 
                CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Year-Month]),'Table'[ID]=130)
                )
        )
        ),
    DISTINCT(
        SELECTCOLUMNS(
            _tb2,"ID","EBIT","Month",'Table'[Year-Month],"Amount",CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Year-Month]),KEEPFILTERS('Table'[ID] IN {130,210,550})))
        ),
    DISTINCT(
        SELECTCOLUMNS(
            _tb2,"ID","EBIT(%)","Month",'Table'[Year-Month],"Amount",
            DIVIDE(CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Year-Month]),KEEPFILTERS('Table'[ID] IN {130,210,550})),
            CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Table','Table'[Year-Month]),'Table'[ID]=130)
            )
        )
        )            
    )

&nbsp;
Then create a measure to display the values for the different rows.
Value = 
    IF(MAX('Contingency table'[ID]) IN {"EBIT(%)","EBITDA(%)"},
    FORMAT(SUM('Contingency table'[Amount]),"#.0%")
    ,SUM('Contingency table'[Amount]))
The final result is as follows, hopefully it will meet your needs.

Please see the attached pbix for reference.Best Regards,Dengliang LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

&nbsp;

Forum Discussion

contingency table with calculated rows

1 Reply

Related content

Direct Lake Model - Calculated Column/Tables

How to search specific word in table filter Calculations

Table Visual not Matching Calculated Visual

Managing BI Updates with Multiple Editors For Contingency

Directlake semantic model, calculated table

Date	ID	Amount	Type
20.04.2023	130	10300	EBITDA
10.05.2023	130	11800	EBITDA
13.04.2023	130	8900	EBITDA
10.05.2023	210	-5400	EBITDA
01.04.2023	210	-6500	EBITDA
13.04.2023	210	-4900	EBITDA
24.05.2023	210	-300	EBITDA
10.05.2023	550	-2500	EBIT
24.05.2023	550	-2300	EBIT
13.04.2023	550	-1900	EBIT