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

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