Forum Discussion

Marico's avatar
Marico
Helper IV
1 year ago
Solved

Combined Matrix

Hi - My sample data looks like the following:

 

SalesInternal/ ExternalCategoryFlag
10InternalCat1Flag 1
20ExternalCat1Flag 1
30InternalCat2Flag 1
40ExternalCat2Flag 1
50InternalCat1Flag 2
60ExternalCat1Flag 2
70InternalCat2Flag 2
80ExternalCat2Flag 2

 

I want to create a combined table like below based on the following two sub tables:

 

CategoryFlag 1Flag 2
InternalExternalInternalExternal
Cat110205060
Cat230407080

 

CategoryTotal no. of sales
InternalExternal
Cat16080
Cat2100120

 

Final output table needed:

 

CategoryTotal no. of salesFlag 1Flag 2
InternalExternalInternalExternalInternalExternal
Cat1608010205060
Cat210012030407080
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Marico ,

     

    The following virtual table can be created with the help of the NATURALINNERJOIN function:

    FlagTable = 
SUMMARIZE(
    'SalesData',
    'SalesData'[Category],
    'SalesData'[Flag],
    'SalesData'[Internal/ External],
    "SalesAmount", SUM('SalesData'[Sales])
)

    TotalSalesTable = 
SUMMARIZE(
    'SalesData',
    'SalesData'[Category],
    'SalesData'[Internal/ External],
    "TotalSales", SUM('SalesData'[Sales])
)

    FinalOutputTable = 
NATURALINNERJOIN(
    SUMMARIZE(
        'SalesData',
        'SalesData'[Category],
        'SalesData'[Internal/ External],
        "TotalSales", SUM('SalesData'[Sales])
    ),
    SUMMARIZE(
        'SalesData',
        'SalesData'[Category],
        'SalesData'[Flag],
        'SalesData'[Internal/ External],
        "FlagSales", SUM('SalesData'[Sales])
    )
)

     

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

7 Replies

  • dharmendars007's avatar
    dharmendars007
    Memorable Member
    1 year ago

    Hello Marico , 

     

    Step1  -You will need to calculate the total sales grouped by Category and Internal/ExternaL.

     

     

    Step 2 - Create a Masure for both Flag1 and Flag2

     

     

     

    Step3 = Create a final Table using summarize then use the same to plot in the table visual like you wish in output

     

     

    If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

     

    Thank You

    Dharmendar S

    LinkedIN 

     

     

  • Marico's avatar
    Marico
    Helper IV
    1 year ago

     Hi dharmendars007 - Sorry, I am new to Power BI, all these calculations so would be created through a "New Table measure"? Alos, can't we directly create the final table using summarize?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    1 year ago

    Hi,

    Your data tables has not been pasted properly in your original post - only the first one is clear.  

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Marico ,

     

    The following virtual table can be created with the help of the NATURALINNERJOIN function:

    FlagTable = 
SUMMARIZE(
    'SalesData',
    'SalesData'[Category],
    'SalesData'[Flag],
    'SalesData'[Internal/ External],
    "SalesAmount", SUM('SalesData'[Sales])
)

    TotalSalesTable = 
SUMMARIZE(
    'SalesData',
    'SalesData'[Category],
    'SalesData'[Internal/ External],
    "TotalSales", SUM('SalesData'[Sales])
)

    FinalOutputTable = 
NATURALINNERJOIN(
    SUMMARIZE(
        'SalesData',
        'SalesData'[Category],
        'SalesData'[Internal/ External],
        "TotalSales", SUM('SalesData'[Sales])
    ),
    SUMMARIZE(
        'SalesData',
        'SalesData'[Category],
        'SalesData'[Flag],
        'SalesData'[Internal/ External],
        "FlagSales", SUM('SalesData'[Sales])
    )
)

     

     

    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.