How to calculate row wise using SUMX and VALUES - ALLSELECTED doesn't seem to work with SUMX/VALUES?

1
votes

I am trying to work out DAX that can be used on table to display 4 columns:

  1. YearMonth

  2. Sales amount (sum of sales amount in the month)

  3. Avg sales amount (average calculated as sum of sales amount divide by total number of months)

  4. Difference between points 2 and 3

I have used table visual with 1st column as YearMonth column from my DateTable. The next 3 columns are as follows:

DAX_SalesAmount = SUM(Sales[Amount])

DAX_AvgMonthlySales = CALCULATE(DIVIDE([DAX_SalesAmount],DISTINCTCOUNT(DateTable[YearMonth])),ALLSELECTED(DateTable))

DAX_DiffSalesAndAvgSales = [DAX_SalesAmount] - [DAX_AvgMonthlySales]

This works correctly for the rows but the total is incorrect as it tries to subtract the TotalSales from AvgSales. So that it is able to work out the logic for each month and then SUM, I tried changing the DAX to:

DAX_DiffSalesAndAvgSales = SUMX(VALUES(DateTable[YearMonth]),[DAX_SalesAmount] - [DAX_AvgMonthlySales])

The above gives 0's in cell and also 0 in total. The reason for 0 is that, both [DAX_SalesAmount] and [DAX_AvgMonthlySales] are evaluating to be same value. Why is [DAX_AvgMonthlySales] not working correctly in SUMX/VALUES (when I have used ALLSELECTED in [DAX_AvgMonthlySales])?

What is the way to correctly do this?

1
powerbidax

1 Answers

1
votes

the problem might be the iterator of SUMX that sets the shadow filter for the ALLSELECTED to the VALUES(DateTable[YearMonths]). Try to save the DAX_AvgMonthlySales to a variable before the iteration like for instance

DAX_DiffSalesAndAvgSales =
VAR AVGSales = [DAX_AvgMonthlySales]
RETURN
    SUMX (
        VALUES ( DateTable[YearMonth] ),
        [DAX_SalesAmount] - AVGSales
    )

This article might also help better understanding what happens with ALLSELECTED()

https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/