contingency table with calculated rows

[Gandhary80]

Hello, 

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. 

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?

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

Thank you in advance for your suggestion of solution. It will help a lot 🙂

František

[Anonymous · Accepted solution]

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)
            )
        )
        )            
    )

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 Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.