PowerPivot 2013 DAX to sum distinct values and return 1 number for all

Question

I need DAX to calculate Brand Total, which would return 1 number per Brand, as a total of distinct values, ignoring week & product filters.

Data sample:

Brand           Product  Week  Weekly_sales 
Brand_1         Prod_1      1    178 
Brand_2         Prod_3      1    7 
Brand_2         Prod_4      1    7 
Brand_2         Prod_5      1    7 
Brand_2         Prod_6      1    7 
Brand_1         Prod_1      2    224 
Brand_2         Prod_3      2    18 
Brand_2         Prod_4      2    18 
Brand_2         Prod_5      2    18 
Brand_2         Prod_6      2    18 
Brand_1         Prod_1      3    186 
Brand_1         Prod_2      3    186 
Brand_2         Prod_3      3    21 
Brand_2         Prod_4      3    21 
Brand_2         Prod_5      3    21 
Brand_2         Prod_6      3    21 
Brand_1         Prod_1      4    172 
Brand_1         Prod_2      4    172 
Brand_2         Prod_3      4    12 
Brand_2         Prod_4      4    12 
Brand_2         Prod_5      4    12 
Brand_2         Prod_6      4    12

The desired output in power pivots is 1 number by brand for any week/product of that brand, inc sub/grand totals i.e. this is what pivot would look like:

I have tried countless combinations of FILTER, ALLEXCEPT, SUMX, and the closest I got to is this:

BRAND total:=
CALCULATE( 
        SUMX( ALLEXCEPT( DATA, 
                        Data[Brand], 
                        Data[Week]),    
             [Vol BR max]),   
        VALUES(Data[Brand]) 
      )

where [Vol BR max] is a measure:

 [Vol BR max]:= MAX(Data[Weekly_sales])

This returns 1 number per brand as I wanted, but it's a wrong total - not one of distinct values by brand:

alejandro zuleta alejandro zuleta · Accepted Answer · 2017-02-16T17:03:06

I think your client's requeriment is a bit fussy considering the structure of the data.

For select the rows to sum up you will have to mark them in a special way based on a distinct count of brands per week, that's something you should handle at the source of your data not in PowerPivot or DAX which should be used only for analysis.

First you will have to create a RowNumber column, in Power Query it is extremely easy, check this Create Index Columns Using Power Query.

Now you have an additional column in your model called RowNumber, which sums 1 by each row (22 rows in your sample), starting from 1:

Now create a calculated column called BrandWeek using this DAX expression:

BrandWeek = [Brand]&"-"&[Week]

Then create another calculated column ShouldSum, that will let us determine which Weekly_sales values should be summed in each week.

ShouldSum =
CALCULATE (
    COUNTA ( Data[BrandWeek] ),
    FILTER (
        Data,
        [BrandWeek] = EARLIER ( Data[BrandWeek] )
            && [RowNumber] <= EARLIER ( Data[RowNumber] )
    )
)

Finally the measure to use in your Pivot is something like this:

BRAND total :=
CALCULATE (
    SUM ( Data[Weekly_Sales] ),
    FILTER (
        ALL ( Data ),
        COUNTROWS (
            FILTER (
                Data,
                EARLIER ( Data[ShouldSum] ) = 1
                    && EARLIER ( Data[Brand] ) = [Brand]
            )
        )
    )
)

This is what you should get:

Note I am using a matrix in Power BI, since I don't have access to Power Pivot right now.

PowerPivot 2013 DAX to sum distinct values and return 1 number for all

1 Answers