Forum Discussion

POSPOS's avatar
POSPOS
Icon for Post Partisan rankPost 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

POSPOS's avatar
POSPOS
Icon for Post Partisan rankPost Partisan
1 year ago

lbendlin  - Thanks for your response. We were looking for having Sales as three different measures (Sales,Sale1 and Sale2)as we cannot use a matrix as there are many more additional columns that we have and also to use these measures for further calculations.

lbendlin's avatar
lbendlin
Icon for Super User rankSuper 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
    Icon for Post Partisan rankPost 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
        Icon for Post Partisan rankPost 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