topic Calculated Row using Power BI DAX in DAX Commands and Tips

Calculated Row using Power BI DAX

Dulay — Thu, 18 Nov 2021 17:14:59 GMT

I have 3 related tables: Actual PL, Item Group and Calendar

My objective is to insert calculated row in Matrix Table

Below measure was created in Actual PL table

IS =

VAR result =

CALCULATE (

SUM ( 'ACTUAL PL'[Value] ),

VALUES ( 'Item Group'[Item Group] ),

VALUES ( 'Calendar'[Date])

)

VAR list =

SUMMARIZE ( ALLSELECTED ( 'ACTUAL PL' ), [Item Group], "Result", result )

RETURN

IF (

result <> BLANK (),

result,

VAR REVENUE =

CALCULATE ( SUM ( 'ACTUAL PL'[Value]), 'Item Group'[Item Group] = "REVENUES")

VAR EXPENSE =

CALCULATE ( -1*SUM ( 'ACTUAL PL'[Value]), 'Item Group'[Item Group] = "EXPENSES")

VAR NCOE =

CALCULATE ( -1*SUM ( 'ACTUAL PL'[Value]), 'Item Group'[Item Group] = "Non-Cash Operating Expenses")

VAR OC =

CALCULATE ( SUM ( 'ACTUAL PL'[Value]), 'Item Group'[Item Group] = "Other Charges (Income)")

VAR PROVISION =

CALCULATE ( -1*SUM ( 'ACTUAL PL'[Value]), 'Item Group'[Item Group] = "Provision for (Benefit From) Income Tax")

VAR CIA =

CALCULATE ( SUM ( 'ACTUAL PL'[Value]), 'Item Group'[Item Group] = "Core Income Adjustments")

RETURN

SWITCH (

SELECTEDVALUE ( 'Item Group'[Item Group] ),

"EBITDA", REVENUE - EXPENSE,

"EBITDA Margin(%)", ((REVENUE - EXPENSE)/REVENUE)*100,

"INCOME BEFORE EQUITY EARNINGS AND OTHERS", REVENUE - EXPENSE - NCOE,

"INCOME BEFORE TAX", REVENUE - EXPENSE - NCOE - OC,

"REPORTED NET INCOME", REVENUE - EXPENSE - NCOE - OC - PROVISION,

"CORE INCOME", REVENUE - EXPENSE - NCOE - OC - PROVISION + CIA,

"CORE INCOME Margin(%)", ((REVENUE - EXPENSE - NCOE - OC - PROVISION + CIA)/REVENUE)*100,

BLANK ()

)

but the calculated row, such as EBITDA, EBITDA Margin(%) is not reflecting in the Matrix Table

output:

this DAX is working in my other project. can't find reason why it's not working in this project

please help.

Re: Calculated Row using Power BI DAX

AlexisOlson — Thu, 18 Nov 2021 19:12:03 GMT

Can you confirm that EBITDA is a row in the column 'Item Group'[Item Group]?

Re: Calculated Row using Power BI DAX

Dulay — Fri, 19 Nov 2021 00:09:00 GMT

Yes. These are the rows in the Item Group table

Re: Calculated Row using Power BI DAX

Anonymous — Wed, 24 Nov 2021 03:41:58 GMT

Hi Dulay

I have checked your code by my sample. I think the logic of your code is correct. There may be something wrong in your filter or calculation. Here I will give you some suggestions.

My sample is as below.

Actual PL:

Item Group

Calendar table is a calculate table.

Calendar = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"QTR","Qtr"&" "&QUARTER([Date]))

Relationship:

I think list part in Var in your code is useless, we can remove it and the code will still work successfully.

My result:

1. Please check your Matric, you should use [Item Group] from "Item Group" table.

2. Please check your code and make sure values in "Switch" function like "EBITDA" are correct. If they are wrong, your measure will return blank.

3. Check the your calculate by "EBITDA", maybe REVENUE - EXPENSE are the same the return to blank.

You can download my sample to get more details.

Best Regards,
Rico Zhou

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

Re: Calculated Row using Power BI DAX

Dulay — Wed, 24 Nov 2021 04:31:09 GMT

Thanks very much Anonymous

Was able to solve this by removing the SORTing of Item Group using SORT Column. Both are in Item Group table.

Your solution is great also.