Combined Matrix

[Marico]

Hi - My sample data looks like the following:

Sales	Internal/ External	Category	Flag
10	Internal	Cat1	Flag 1
20	External	Cat1	Flag 1
30	Internal	Cat2	Flag 1
40	External	Cat2	Flag 1
50	Internal	Cat1	Flag 2
60	External	Cat1	Flag 2
70	Internal	Cat2	Flag 2
80	External	Cat2	Flag 2

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

Category	Flag 1	Flag 2
Internal	External	Internal	External
Cat1	10	20	50	60
Cat2	30	40	70	80

Category	Total no. of sales
Internal	External
Cat1	60	80
Cat2	100	120

Final output table needed:

Category	Total no. of sales	Flag 1	Flag 2
Internal	External	Internal	External	Internal	External
Cat1	60	80	10	20	50	60
Cat2	100	120	30	40	70	80

[Anonymous · Accepted solution]

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.

[Ashish_Mathur]

Hi,

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

[Marico]

 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?

[dharmendars007]

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]

@dharmendars007 I am not able to create the matrix like the below from output of summarize table:

Could you please share a sample file? Thanks a lot in advance.

[Marico]

@lbendlin @Greg_Deckler @Ritaf1983 @rajendraongole1 @parry2k Any thoughts on above?

[lbendlin]

Your desired output doesn't seem to make sense. What would you change on the below (which was done without coding)