grouping and summary calculation

[sangdelattre]

Hi all

is there a way to force the grouping summary calculation to be sum of the "leaves" values?

I have following filter configuration_table

filter 1, filter 2, ... grouping header, grouping subheader

filter 1 criteria 1, filter 2 criteria 1, ...  grouping header 1, grouping header 1

filter 1 criteria 2, filter 2 criteria 2, ...  grouping header 1, grouping header 1

filter 1 criteria 3, filter 2 criteria 3, ...  grouping header 2, grouping header 2, group header xxx

I use filter column to define what filter should be applied and use grouping to define how data should be grouped in a pivot table.

measure calculation is: my measure =  calculate( xxx, data_column1=configuration_table[filter1], ...)

measure is then used in a pivot table where

rows contains: grouping header 1, grouping header 2

measure: my measure

all my "leaves" calculation are correct. leaves could be at different leave (grouping header 2,3, ...), but the grouping summary are incorrects.

reasion being is that is seems that when calculating the grouping summary value, DAX aggregate my filtering criteria to calculate group header 1 summary, instead of simply doing the sum of grouping header 2. Problem is that aggregating filtering criteria is wrong.

is there a way to force the grouping summary calculation to be sum of the "leaves" values?

I am open to discussion if you think there is better way to proceed.

Thanks in advance for you insight

Sang

[Fowmy]

@sangdelattre 

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

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

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[sangdelattre]

Hi.

In fact I managed to find a solution using the AddColumn inmy configuration table.

Thanks.

[sangdelattre]

hi Fowmy

Please find link to access sample PBI and XL file here.

Expected result in 6 for header1 grouping summary.

My measures are a bit more complex than in the sample BI but I guess you understand my issues (see initial post). I tried using Addcolumn, which work in my simplied case (cf. Actual 2 measure), but does not work for the more complex (not sure why though)

If you have other suggestions to achieve similar result.

I look forward your input