Issue with Row Subtotal and individual rows values sum is not matching in metrix for same formula.

[nayan28apr]

Hi,

I am facing issue to get correct formula response in matrix at each drill down level.

What I found is sum of a, b, c columns are right at my first level and second level of drill down. Also row subtotal is matching fine.

But when i try to create a calculated measure, which does d = (a/b)*c

than row subtotal is applying the same formula and invidividal rows applying the same formula but summed up number does match.

i tried to catch this with SumX, when more than 1 cluster values than do sumx and it works fine
but i wanted to know why this happens? if we can always consider sum of second level or last level of hierarchy and show summed up values when on drill 1 level, does it solve the purpose without worrying about columns?

Below formula to catch both the scnarios, when more than 1 week is there or when only one week and multiple regions there.

Note -- [YTD Weekly Vs. Expected (ss)] my main measure which do d = (a/b)*c

YTD Weekly Vs. Expected (adj)  =

VAR __table5 = SUMMARIZE('SonarSabo',[week],"__value",[YTD Weekly Vs. Expected (ss)])

VAR __table555 = SUMMARIZE('SonarSabo',SonarSabo[region_franchise_comb],SonarSabo[week],"__value",[YTD Weekly Vs. Expected (ss)])

Summarized(Based on Week and Region) =

IF(HASONEVALUE(SonarSabo[week]),

IF( HASONEVALUE( 'SonarSabo'[region_franchise_comb] ),

[YTD Weekly Vs. Expected (ss)],SUMX(__table555,[__value])),

IF( HASONEVALUE( 'SonarSabo'[region_franchise_comb] ),

SUMX(__table5,[__value]),SUMX(__table555,[__value]))

)

The problem amplifies for me when i do same calculation week levels like w14,w15 and so on.

Also not only Region or Cluster drill down, but also Franchise and Segment drill down available in my report on which i want to show same d = (a/b)*c

Similar way as previous formula, i have to replace region franchise column now with franchise column to get summarized values on row subtotal again my writing another measure. This is very much slow and pathetic.

Is there any workaround for this?

Thanks for your help!

[Anonymous]

HI @nayan28apr,

Can you please provide a pbix file with some dummy data and keep the raw table scheme? It should help us clarify your sicario and test to coding formula on it.

How to Get Your Question Answered Quickly

Notice: please not attach the sensitive data in the sample file.

Regards,

Xiaoxin Sheng

[nayan28apr · Accepted solution]

Thank you very much.

Measure=
VAR summarytable = SUMMARIZE('Table',[Segment],[Franchise],[Cluster],[weekno],"_value",[Sales])
Return

SUMX(summarytable ,[__value])

This formula finally worked for me, I need to use all columns in summarize.
I have than used AddColumns with Summarize to find best performance out of it as Summarize was giving me performance issues.

Measure=
VAR summarytable = AddColumns(SUMMARIZE('Table',[Segment],[Franchise],[Cluster],[weekno]),"_value",[Sales])
Return
SUMX(summarytable ,[__value])


Overall I have stopped row subtotal calculation (a/b)*c which was not working for me. Instead used individual rows sum and showing at the subtotal rows.

[lbendlin]

I think you want to read up a bit on this DAX function

ROLLUPADDISSUBTOTAL function (DAX) - DAX | Microsoft Docs