How to use Summarize for multiple columns in different metrix to show correct row subtotal

[nayan28apr]

Hi,

I have four columns in my table.

Region, Cluster, Franchise, Segment

Below measure work fine when i use region in metric graph and it gives correct row subtotal.

Measure=

VAR summarytable = SUMMARIZE('Table',[weekno],"_value",[Sales])

Return

IF(HASONEVALUE(Table[Region]), [Sales], SUMX(summarytable ,[__value]))

How can i make this one formula genric for other fields like franchise, segment or cluster too?
Need to show correct row subtotal in other metrix graphs.

[v-kelly-msft]

Hi  @nayan28apr ，

Modify your measure as below:

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

IF(HASONEVALUE(Table[weekno]), [Sales], SUMX(summarytable ,[__value]))

And you will see:

Check my sample .pbix file attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[nayan28apr · Accepted solution]

Thanks for your inputs.

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])

[amitchandak]

@nayan28apr , Calculation groups is one solution.

There is a set of blogs from sqlbi, you can find other links on this blog.

calculation groups
https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/