DAX: Distinct and then aggregate twice

Question

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?

OscarLar · Answer 1 · 2019-11-21T09:31:56.223

1

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.

edited Nov 21 '19 at 09:31

answered Nov 21 '19 at 06:50

OscarLar

1,315
1
5
15

thanks for providing the answer, I think you understood my question perfectly. I think this is very close, except the Distinct part is not working. I.e., it added the Value without having done Distinct first and therefore the "sum_month" blew up. Not sure why the first Summarize isn't working... any ideas? – DJL Nov 21 '19 at 08:02
Try changning the name of your column [Value]. This is the default name of a new calculated column in pbi and this might screw thing up. I realised my code did not work either. However, after changing the name of the [Value] column to [Value_test] it semed to worked better. – OscarLar Nov 21 '19 at 08:16
In my real data, Value has a different name, it was just an example name. Is there any other way I can achieve DISTINCT? – DJL Nov 21 '19 at 08:21
Realised I'd made a few conceptual errors at first( which my testdata hid from me). I've updated my suggestion and I think this does the trick. – OscarLar Nov 21 '19 at 09:33

score 1 · Answer 2 · answered Nov 21 '19 at 09:03

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

DAX: Distinct and then aggregate twice

2 Answers2