Forum Discussion

POSPOS's avatar
POSPOS
Post Partisan
1 year ago
Solved

How to create a summary table

Hi All, I have three tables (Master, Source 1, Source 2). Based on the tables, my requriement is to create a summary table: Sample file -Download Master Table: Dept Div Sales Dept A Div...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi POSPOS ,

     

    Please create a new table :

    And 3 measures:

     

    Sales 1 = 
VAR __curr_dept = MAX('Table'[Dept])
VAR __curr_div = MAX('Table'[Div])
VAR __result = IF( HASONEVALUE('Table'[Dept]), CALCULATE(SUM('Master'[Sales]),'Master'[Dept]=__curr_dept&&'Master'[Div]=__curr_div), SUM('Master'[Sales]))
RETURN
    __result
    Sales 2 = 
VAR __curr_dept = MAX('Table'[Dept])
VAR __curr_div = MAX('Table'[Div])
VAR __sales = IF(HASONEVALUE('Table'[Dept]), CALCULATE(SUM('Source 1'[Sales]),'Source 1'[Dept]=__curr_dept, 'Source 1'[Div]=__curr_div), SUM('Source 1'[Sales]))
VAR __result = __sales - [Sales 1]
RETURN
    __result
    Sales 3 = 
VAR __curr_dept = MAX('Table'[Dept])
VAR __curr_div = MAX('Table'[Div])
VAR __sales = IF( HASONEVALUE('Table'[Dept]), CALCULATE(SUM('Source 2'[Sales]),'Source 2'[Dept]=__curr_dept, 'Source 2'[Div]=__curr_div), SUM('Source 2'[Sales]))
VAR __result = __sales - [Sales 1]
RETURN
    __result

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

lbendlin's avatar
lbendlin
Super User
1 year ago

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

POSPOS's avatar
POSPOS
Post Partisan
1 year ago

lbendlin  - Expected output is as provided in the post above where We will need three measures separately.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi POSPOS ,

     

    Please create a new table :

    And 3 measures:

     

    Sales 1 = 
VAR __curr_dept = MAX('Table'[Dept])
VAR __curr_div = MAX('Table'[Div])
VAR __result = IF( HASONEVALUE('Table'[Dept]), CALCULATE(SUM('Master'[Sales]),'Master'[Dept]=__curr_dept&&'Master'[Div]=__curr_div), SUM('Master'[Sales]))
RETURN
    __result
    Sales 2 = 
VAR __curr_dept = MAX('Table'[Dept])
VAR __curr_div = MAX('Table'[Div])
VAR __sales = IF(HASONEVALUE('Table'[Dept]), CALCULATE(SUM('Source 1'[Sales]),'Source 1'[Dept]=__curr_dept, 'Source 1'[Div]=__curr_div), SUM('Source 1'[Sales]))
VAR __result = __sales - [Sales 1]
RETURN
    __result
    Sales 3 = 
VAR __curr_dept = MAX('Table'[Dept])
VAR __curr_div = MAX('Table'[Div])
VAR __sales = IF( HASONEVALUE('Table'[Dept]), CALCULATE(SUM('Source 2'[Sales]),'Source 2'[Dept]=__curr_dept, 'Source 2'[Div]=__curr_div), SUM('Source 2'[Sales]))
VAR __result = __sales - [Sales 1]
RETURN
    __result

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • POSPOS's avatar
      POSPOS
      Post Partisan
      1 year ago

      Anonymous  - Thank you for your response. This is very helpful. 
      Could you please help me tweak the logic for Sales 2 and Sales 3 to only when the below condition is satisfied?

      Sale 2 = Sale 2 from Source 1 difference Sales value from Master Table only for the Dept which is common in "Source 1" and "Master Table" and Div should not exist in "Master" Table

       

      Sale 3 = Sale 3 from Source 2 difference Sales value from Master Table only for the Div which is common in "Source 1" and "Master Table" and Dept should not exist in Master Table

    • POSPOS's avatar
      POSPOS
      Post Partisan
      1 year ago

      Anonymous  - This worked and saved a lot of our development effort. Much appreciated for the detailed steps. ğŸ™‚

    • POSPOS's avatar
      POSPOS
      Post Partisan
      1 year ago

      Anonymous  - When I use the above measures in another calcuated column, then I get circular dependency error.
      eg: column = if( sales 2 <>0, "Y","N")

      Can you please suggest on to fix this.