topic Re: Trouble with Summarization in DAX Commands and Tips

Trouble with Summarization

Anonymous — Mon, 11 Nov 2024 21:17:58 GMT

Hello Community,

I am having some issues with a particular summarization I am trying to do. I am trying to create a summarized table for the measure $ share change and am having some difficulties with the summarization that I am not sure how to address. If you see below, you'll see that the "sum of $ share change" column is all 0.00% when it should match the "retail $ share change" figures next to it. You can also see below how I currently created the summarized table. Any assistance here would be very much appreciated. Thank you so much!

Re: Trouble with Summarization

Poojara_D12 — Tue, 12 Nov 2024 07:05:01 GMT

Hi Anonymous

It sounds like your summarized table isn't correctly calculating the measure $ Share Change in the "Sum of $ Share Change" column. This could be due to the way the DAX code is written in the summarized table, or it might be caused by a missing or misapplied filter context. Let’s work through a few troubleshooting steps and adjust the DAX so that the "Sum of $ Share Change" column properly reflects the Retail $ Share Change values.

1. Review the $ Share Change Measure

First, double-check the DAX expression for your $ Share Change measure to ensure that it isn’t inadvertently zeroing out when calculated in a different filter context. A typical $ Share Change measure might look like this:

DAX $ Share Change = [Current Year Retail $] - [Previous Year Retail $]

Or, if calculated as a percentage:

$ Share Change = DIVIDE([Current Year Retail $] - [Previous Year Retail $], [Previous Year Retail $], 0)

Make sure the measure correctly calculates $ Share Change at the level you need, without being influenced by other slicers or filters. Once verified, you can move to create the summarized table.

2. Adjust the Summarized Table DAX Code

Use the SUMMARIZE function to build your summarized table and ensure the correct calculation of the $ Share Change measure within this context. Here’s how you might adjust the DAX to ensure that the "Sum of $ Share Change" correctly matches the "Retail $ Share Change" values.

Assuming that you are summarizing by categories like Product and Region, here’s an example DAX formula:

DAX Summarized Table = SUMMARIZE( 'SalesData', 'SalesData'[Product], 'SalesData'[Region], "Retail $ Share Change", [$ Share Change], -- This pulls in the measure "Sum of $ Share Change", CALCULATE( [$ Share Change], ALLEXCEPT('SalesData', 'SalesData'[Product], 'SalesData'[Region]) ) )

3. Check Filters or Slicers Applied to the Visual

If you still see zero values, check if any filters or slicers are affecting the table visualization or the summarized table’s calculation. Filters on dates, regions, or other factors can sometimes result in $ Share Change returning 0 if they unintentionally filter out necessary data.

Alternative Using ADDCOLUMNS (if SUMMARIZE Doesn’t Work)

If SUMMARIZE continues to give incorrect results, you can try using ADDCOLUMNS with SUMMARIZE for more control:

Summarized Table = ADDCOLUMNS( SUMMARIZE( 'SalesData', 'SalesData'[Product], 'SalesData'[Region] ), "Retail $ Share Change", [$ Share Change], "Sum of $ Share Change", CALCULATE( [$ Share Change], ALLEXCEPT('SalesData', 'SalesData'[Product], 'SalesData'[Region]) ) )

This approach also groups by Product and Region and calculates both $ Share Change columns within that grouping.

These steps should align the "Sum of $ Share Change" with the "Retail $ Share Change" column values. Let me know if you need further customization!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant

Re: Trouble with Summarization

Anonymous — Tue, 12 Nov 2024 16:00:11 GMT

Hi Poojara!

Thank you so much for the help here. It still looks like I'm getting all zero values with the approaches mentioned above. Please see my summarization here:

KO $ Share Change Rank =

ADDCOLUMNS(

SUMMARIZE (

'Nielsen (excl. dist)',

'Nielsen (excl. dist)'[Manufacturer],

'Nielsen (excl. dist)'[Sub-Category],

'Nielsen (excl. dist)'[Zone],

'Nielsen (excl. dist)'[Channel]),

"KO $ Share Change", [KO Retail $ Share Change],

"Sum of KO $ Share Change",

CALCULATE ([KO Retail $ Share Change]))

And here is my original $ share change formula:

KO Retail $ Share Change = iferror([KO Retail $ Share %]-[KO Retail $ Share % YA],0)

Also see below what I'm getting versus what the figures should be:

Thank you so much in advance for all of your help!

Best,

Maggie

Re: Trouble with Summarization

Anonymous — Thu, 14 Nov 2024 07:17:02 GMT

Hi Anonymous ,

Based on the description, try using the following DAX formula.

KO $ Share Change Rank = ADDCOLUMNS( SUMMARIZE( 'Nielsen (excl. dist)', 'Nielsen (excl. dist)'[Manufacturer], 'Nielsen (excl. dist)'[Sub-Category], 'Nielsen (excl. dist)'[Zone], 'Nielsen (excl. dist)'[Channel] ), "KO $ Share Change", [KO Retail $ Share Change], "Sum of KO $ Share Change", SUMX('Nielsen (excl. dist)', [KO Retail $ Share Change]) )

If the calculation is still 0, please provide the data from the original table.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.