Forum Discussion
Anmolgan
Post Prodigy
Post ProdigyTranspose Matric with two measure per columns?
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
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?
2 Replies
- lbendlin
Super User
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?
