Matrix divide one row by another

[ChavdarG]

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

[v-jingzhang · Accepted solution]

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.

[ChavdarG]

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

[vanessafvg]

Hi @ChavdarG 

are you able to provide some sample data in a text format?

[ChavdarG]

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,212

Thank you,

Chavdar