Forum Discussion

Dulay's avatar
Dulay
New Member
4 years ago
Solved

Calculated Row using Power BI DAX

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.

 

  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

4 Replies

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

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

  • Dulay's avatar
    Dulay
    New Member
    4 years ago

    Yes.  These are the rows in the Item Group table

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

       

      • Dulay's avatar
        Dulay
        New Member
        4 years ago

        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.