CODEWITHSUNDEEP
group-bypowerbiaggregatedistinctdax
DJL
DJL

Reputation: 144

DAX: Distinct and then aggregate twice

I'm trying to create a Measure in Power BI using DAX that achieves the below.

The data set has four columns, Name, Month, Country and Value. I have duplicates so first I need to dedupe across all four columns, then group by Month and sum up the value. And then, I need to average across the Month to arrive at a single value. How would I achieve this in DAX?

Upvotes: 1

Views: 781

Answers (2)

OscarLar
OscarLar

Reputation: 1335

Not sure I completeley understood the question since you didn't provide example data or some DAX code you've already tried. Please do so next time.

I'm assuming parts of this can not (for reasons) be done using power query so that you have to use DAX. Then I think this will do what you described.

Create a temporary data table called Data_reduced in which duplicate rows have been removed. 

Data_reduced = 
SUMMARIZE(
    'Data';
    [Name];
    [Month];
    [Country];
    [Value]
)

Then create the averaging measure like this

AveragePerMonth = 
AVERAGEX(  
    SUMMARIZE( 
        'Data_reduced';
        'Data_reduced'[Month];
        "Sum_month"; SUM('Data_reduced'[Value])
    );
    [Sum_month]
)

Where Data is the name of the table.

Upvotes: 1

DJL
DJL

Reputation: 144

I figured it out. Reply by @OscarLar was very close but nested SUMMARIZE causes problems because it cannot aggregate values calculated dynamically within the query itself (https://www.sqlbi.com/articles/nested-grouping-using-groupby-vs-summarize/).

I kept the inner SUMMARIZE from @OscarLar's answer changed the outer SUMMARIZE with a GROUPBY. Here's the code that worked.

AVERAGEX(GROUPBY(SUMMARIZE(Data, Data[Name], Data[Month], Data[Country], Data[Value]), Data[Month], "Month_Value", sumx(CURRENTGROUP(), Data[Value])), [Month_Value])

Upvotes: 1

Related Questions