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

  • POSPOS's avatar
    POSPOS
    Post Partisan
    1 year ago

    lbendlin  I was referrning to "Minus" as "Difference"/"Substraction" between two values.

    Dept = Union of all values from Master, Source 1, Source 2 tables

    Div =  Union of all values from Master, Source 1, Source 2 tables
    Sales 1=  Sales value from Master Table

    Sale 2 = Sale 2 from Source 1 difference Sales value from Master Table 

    Sale 3 = Sale 3 from Source 2 difference Sales value from Master Table

    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago

      I used EXCEPT but that removes the exact matches - you will need to modify that to remove the difference regardless of exact matches.

      • POSPOS's avatar
        POSPOS
        Post 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.