Forum Discussion
Matrix divide one row by another
Hello everyone,
I have been at this for some time now and finally I decided to seek help from the Community.
I have seen similar questions posted but for some reason am unable to adapt the answers to my particular case.
So, hopefully, someone can solve it for my specific situation.
I have a matrix, which looks like this with numbers per attribute (row) for Previous Year/Plan/Latest Estimate (columns):
I need to get to the exact same table (could be in the same table or a new one to be placed below this one, whichever solutions is easiest), which shows the NSR per item sold i.e. NSR absolute value / Volumes and thus for every attribute down to EBIT.
My data is organized in this way:
12 months of Volumes for each market, 12 months of NSR for each market etc.
The table with the absolute values is generated as follows:
If something is still unclear, let me know.
Appreciate your help.
Chavdar
Hi ChavdarG
Do you mean the denominator will always be Volumes value and the numerator will change according to the attribute? If so, try below measure.
Measure = IF ( SELECTEDVALUE ( Attributes[Attribute] ) = "VOLUMES", SUM ( 'Table'[Final_Value] ), DIVIDE ( SUM ( 'Table'[Final_Value] ), CALCULATE ( SUM ( 'Table'[Final_Value] ), ALL ( 'Table' ), 'Table'[Attribute] = "VOLUMES" ) ) )Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
4 Replies
- vanessafvg
Community Champion
Hi Vanessa,
Will this do? Use Final Value column:
Region Zone Cost_Centre Market Attribute Value Month Final_Value
Region1 Zone1 1 Market1 EBIT 122,511 3 122,511
Region1 Zone1 1 Market1 ADMIN_COST -2,712 3 -2,712
Region1 Zone1 1 Market1 TRADE_RESULT 125,223 3 125,223
Region1 Zone1 1 Market1 STRUCTURE_COST -1,325 3 -1,325
Region1 Zone1 1 Market1 BRAND_CONTRIBUTION 126,548 3 126,548
Region1 Zone1 1 Market1 TOTAL_MI -4,344 3 -4,344
Region1 Zone1 1 Market1 COMMISSIONS -4,344 3 -4,344
Region1 Zone1 1 Market1 DIRECT_COST -1,217 3 -1,217
Region1 Zone1 1 Market1 GP 132,108 3 132,108
Region1 Zone1 1 Market1 OTHERS -403 3 -403
Region1 Zone1 1 Market1 COGS -34,486 3 -34,486
Region1 Zone1 1 Market1 NSR 166,997 3 166,997
Region1 Zone1 1 Market1 GSR 166,997 3 166,997
Region1 Zone1 1 Market1 VOLUMES 320 3 320
Region1 Zone1 1 Market1 EBIT 133,636 6 133,636
Region1 Zone1 1 Market1 ADMIN_COST -2,712 6 -2,712
Region1 Zone1 1 Market1 TRADE_RESULT 136,348 6 136,348
Region1 Zone1 1 Market1 STRUCTURE_COST -1,325 6 -1,325
Region1 Zone1 1 Market1 BRAND_CONTRIBUTION 137,672 6 137,672
Region1 Zone1 1 Market1 TOTAL_MI -3,959 6 -3,959
Region1 Zone1 1 Market1 COMMISSIONS -3,959 6 -3,959
Region1 Zone1 1 Market1 DIRECT_COST -1,016 6 -1,016
Region1 Zone1 1 Market1 GP 142,647 6 142,647
Region1 Zone1 1 Market1 OTHERS -123 6 -123
Region1 Zone1 1 Market1 COGS -36,263 6 -36,263
Region1 Zone1 1 Market1 NSR 179,033 6 179,033
Region1 Zone1 1 Market1 GSR 179,033 6 179,033
Region1 Zone1 1 Market1 VOLUMES 340 6 340
Region3 Zone3 3 Market3 EBIT -271,733 1 -271,733
Region3 Zone3 3 Market3 ADMIN_COST -116,676 1 -116,676
Region3 Zone3 3 Market3 TRADE_RESULT -155,056 1 -155,056
Region3 Zone3 3 Market3 STRUCTURE_COST -327,673 1 -327,673
Region3 Zone3 3 Market3 BRAND_CONTRIBUTION 172,617 1 172,617
Region3 Zone3 3 Market3 TOTAL_MI -155,102 1 -155,102
Region3 Zone3 3 Market3 A&P -155,102 1 -155,102
Region3 Zone3 3 Market3 DIRECT_COST 10,807 1 10,807
Region3 Zone3 3 Market3 GP 316,912 1 316,912
Region3 Zone3 3 Market3 OTHERS -26,150 1 -26,150
Region3 Zone3 3 Market3 COGS -1,172,344 1 -1,172,344
Region3 Zone3 3 Market3 NSR 1,515,406 1 1,515,406
Region3 Zone3 3 Market3 GSR 1,515,406 1 1,515,406
Region3 Zone3 3 Market3 VOLUMES 8,920 1 8,920
Region3 Zone3 3 Market3 EBIT -482,960 6 -482,960
Region3 Zone3 3 Market3 ADMIN_COST -116,676 6 -116,676
Region3 Zone3 3 Market3 TRADE_RESULT -366,284 6 -366,284
Region3 Zone3 3 Market3 STRUCTURE_COST -327,673 6 -327,673
Region3 Zone3 3 Market3 BRAND_CONTRIBUTION -38,611 6 -38,611
Region3 Zone3 3 Market3 TOTAL_MI -450,349 6 -450,349
Region3 Zone3 3 Market3 RESEARCH -15,906 6 -15,906
Region3 Zone3 3 Market3 A&P -434,443 6 -434,443
Region3 Zone3 3 Market3 DIRECT_COST 2,981 6 2,981
Region3 Zone3 3 Market3 GP 408,758 6 408,758
Region3 Zone3 3 Market3 OTHERS -10,515 6 -10,515
Region3 Zone3 3 Market3 COGS -1,312,043 6 -1,312,043
Region3 Zone3 3 Market3 NSR 1,731,316 6 1,731,316
Region3 Zone3 3 Market3 GSR 1,731,316 6 1,731,316
Region3 Zone3 3 Market3 VOLUMES 10,212 6 10,212Thank you,
Chavdar
v-jingzhangCommunity Support
Hi ChavdarG
Do you mean the denominator will always be Volumes value and the numerator will change according to the attribute? If so, try below measure.
Measure = IF ( SELECTEDVALUE ( Attributes[Attribute] ) = "VOLUMES", SUM ( 'Table'[Final_Value] ), DIVIDE ( SUM ( 'Table'[Final_Value] ), CALCULATE ( SUM ( 'Table'[Final_Value] ), ALL ( 'Table' ), 'Table'[Attribute] = "VOLUMES" ) ) )Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.Hello,
Yes, this seems to do the trick. Thank you. I managed to do it by re-organizing my data into a folder and combining it into a big table that is much slower to load. But this way I could do the above with simple measures and finally by using the matrix values to rows option.
Still, much appreciated that you came back with this solution. Now I can think whether or not to go back to the separate data variant, where everything is cleaner and loads faster.
Thanks,
Chavdar
