Transpose Matric with two measure per columns?

[Anmolgan]

Below is the format that I want to show in Power BI:

Above is the format that I want to show in Power BI, now I have done most of the things by designing transpose matrix, one issue that I am facing is am not able to figure out how can I show Billed quantity and Net Realization value per single column of automative, industrial etc.

Below is what I have build so far:

Designing individual measures as below:

Channel Sales Group Retail = CALCULATE([Divide BilledQuantity], FILTER('Mapping 1', 'Mapping 1'[Sort Column]= 1), FILTER('Master Sales Report', 'Master Sales Report'[Material group.Material group Level 01.Key]<> "05"))

Channel Sales OEM/FWS = CALCULATE([Divide BilledQuantity], FILTER('Mapping 1', 'Mapping 1'[Sort Column]= 2), FILTER('Master Sales Report', 'Master Sales Report'[Material group.Material group Level 01.Key]<> "05"))

IMF = CALCULATE([Divide BilledQuantity], FILTER('Mapping 1', 'Mapping 1'[Sort Column]= 3), FILTER('Master Sales Report', 'Master Sales Report'[Material group.Material group Level 01.Key]<> "05"))

Total AdBlue Sales = var a= CALCULATE([Divide BilledQuantity], FILTER('Mapping 1', 'Mapping 1'[Sort Column]= 1))

var b= CALCULATE([Divide BilledQuantity], FILTER('Mapping 1', 'Mapping 1'[Sort Column]= 2))

var c= CALCULATE([Divide BilledQuantity], FILTER('Mapping 1', 'Mapping 1'[Sort Column]= 3))

return a+b+c

Total Except Adblue = [Channel Sales Group Retail] + [Channel Sales OEM/FWS] + [IMF]

Designign Data Table for transpose matrix and then using below measure to drive:

Measure Display =

IF (

HASONEVALUE ( 'Business Table'[Measure]),

SWITCH ( VALUES ( 'Business Table'[Measure] ),

"Channel Sales Group Retail", [Channel Sales Group Retail],

"Channel Sales OEM/FWS", [Channel Sales OEM/FWS],

"IMF", [IMF],

"Total Adblue Sales",[Total AdBlue Sales],

"Total Except Adblue", [Total Except Adblue]

)

)

How can I also include net realization in the above formula so that there are 2 columns of billed quantity and net realization per material groups those are Automotive, Industrial, STC etc.

Any help will be appreciated

[lbendlin · Accepted solution]

Repeat the whole process for the net realization measure and then add both measures to the values well of the matrix visual.

Have you considered Calculation Groups?

[Anmolgan]

@lbendlin thanks for the answer, how can i best use calculation groups here any suggestions?