Forum Discussion

jkhan's avatar
jkhan
Helper III
5 years ago
Solved

DAX Help

Dear Power BI Gurus,

 

Please help to build below DAX, I need to make sum based on condition on different Tables and Sum them together 

I tried to use below DAX but not working. 

 

TOTAL IFC ALL REVENUE ACTUAL = CALCULATE ( SUM(IFC_Monthly[Value]),IFC_Monthly[Category]="Revenue", IFC_Monthly[Period]="Actual"
+ SUM(CPU_Monthly[Value]),CPU_Monthly[Category]="Revenue", CPU_Monthly[Period]="Actual"
+ SUM(LOUNGE_Monthly[Value]),LOUNGE_Monthly[Category]="Revenue", LOUNGE_Monthly[Period]="Actual"
+ SUM(AIRLINES_Monthly[Value]),AIRLINES_Monthly[Category]="Revenue", AIRLINES_Monthly[Period]="Actual"
+ SUM(MD_Monthly[Value]),MD_Monthly[Category]="Revenue", MD_Monthly[Period]="Actual"
)

 

Need Help DAX
  • Fowmy's avatar
    Fowmy
    5 years ago

    jkhan 

    Can you use this measure which I modified.

    TOTAL IFC ALL REVENUE ACTUAL =
VAR ifc =
    CALCULATE (
        SUM ( IFC_Monthly[Value] ),
        IFC_Monthly[Category] = "Revenue",
        IFC_Monthly[Period] = "Actual"
    )
VAR cpu =
    CALCULATE (
        SUM ( CPU_Monthly[Value] ),
        CPU_Monthly[Category] = "Revenue",
        CPU_Monthly[Period] = "Actual"
    )
VAR lounge =
    CALCULATE (
        SUM ( LOUNGE_Monthly[Value] ),
        LOUNGE_Monthly[Category] = "Revenue",
        LOUNGE_Monthly[Period] = "Actual"
    )
VAR md =
    CALCULATE (
        SUM ( MD_Monthly[Value] ),
        MD_Monthly[Category] = "Revenue",
        MD_Monthly[Period] = "Actual"
    )
RETURN
    ifc + cpu + lounge + md

4 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    jkhan 

    You cannot add anything to the filter within CALCULATE like you have done + SUM ( CPU_Monthly[Value] ) .
    Can you explain what is the logic that you need to apply to do the calculation also show picture of your model.




  • jkhan's avatar
    jkhan
    Helper III
    5 years ago

    Hi Fowmy,

     

    I need aggregiate Sum from Different Tables like  IFC_Monthly CPU_Monthly LOUNGE_Monthly based on condidtion 
    IFC_Monthly[Category]="Profit", IFC_Monthly[Period]="Actual"

     

     

     

    • Fowmy's avatar
      Fowmy
      Super User
      5 years ago

      jkhan 

      Can you use this measure which I modified.

      TOTAL IFC ALL REVENUE ACTUAL =
VAR ifc =
    CALCULATE (
        SUM ( IFC_Monthly[Value] ),
        IFC_Monthly[Category] = "Revenue",
        IFC_Monthly[Period] = "Actual"
    )
VAR cpu =
    CALCULATE (
        SUM ( CPU_Monthly[Value] ),
        CPU_Monthly[Category] = "Revenue",
        CPU_Monthly[Period] = "Actual"
    )
VAR lounge =
    CALCULATE (
        SUM ( LOUNGE_Monthly[Value] ),
        LOUNGE_Monthly[Category] = "Revenue",
        LOUNGE_Monthly[Period] = "Actual"
    )
VAR md =
    CALCULATE (
        SUM ( MD_Monthly[Value] ),
        MD_Monthly[Category] = "Revenue",
        MD_Monthly[Period] = "Actual"
    )
RETURN
    ifc + cpu + lounge + md
      • jkhan's avatar
        jkhan
        Helper III
        5 years ago

        Hi Fowmy,

        I used like below, Thanks for your reply. 

         

        TOTAL IFC ALL PROFIT ACTUAL =
        CALCULATE ( SUM(IFC_Monthly[Value]),IFC_Monthly[Category]="Profit", IFC_Monthly[Period]="Actual" )
        +
        CALCULATE (SUM(CPU_Monthly[Value]),CPU_Monthly[Category]="Profit", CPU_Monthly[Period]="Actual" )
        +
        CALCULATE (SUM(LOUNGE_Monthly[Value]),LOUNGE_Monthly[Category]="Profit", LOUNGE_Monthly[Period]="Actual" )
        +
        CALCULATE (SUM(AIRLINES_Monthly[Value]),AIRLINES_Monthly[Category]="Profit", AIRLINES_Monthly[Period]="Actual" )
        +
        CALCULATE (SUM(MD_Monthly[Value]),MD_Monthly[Category]="Profit", MD_Monthly[Period]="Actual" )