Forum Discussion
DAX total issue
Hi, Im actually working on Excel side (PowerBi is not an option for this), but: I have a Power Pivot going with a measure: =CALCULATE(SUM([Total value]),SalesData[Quoted]>1) > This will ...
CarlsBerg999 Try this
=
SUMX (
VALUES ( Table[Qtr] ),
CALCULATE ( SUM ( [Total value] ), SalesData[Quoted] > 1 )
)
Thank you, it works but limitedly. There is a year-hierarchy here as well but the dax formula filters only by quarter. Therefore if i select Q2 of 2020, it will give me the sum of all Q2's (for example Q2 2021 + Q2 2020), even though i only want Q2 of 2020.
Is there a way to modify the formula to include the year as well? Im unable to modify it with my current knowledge.
Basically the problem is present in the picture below, i have filtered Q1 2020 which has 30 000€ of quotes. The sum is 290 000€ because Q1 2021 has 260 000€ of quotes.
CarlsBerg999 In that case create a column in the date table that has both Quarter and Year number like : Q1 2020
and then use
= SUMX ( VALUES ( Table[Year Qtr] ), CALCULATE ( SUM ( [Total value] ), SalesData[Quoted] > 1 ) )Or modify the DAX code to
= SUMX ( VALUES ( Table[Qtr] ), CALCULATE ( SUM ( [Total value] ), SalesData[Quoted] > 1, Dates[Calendar Year] = 2020 ) )
