Forum Discussion

CarlsBerg999's avatar
CarlsBerg999
Icon for Helper V rankHelper V
6 years ago

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 ...
Need Help
CarlsBerg999's avatar
CarlsBerg999
Icon for Helper V rankHelper V
6 years ago

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_0-1598797211890.png

AntrikshSharma's avatar
AntrikshSharma
Icon for Community Champion rankCommunity Champion
6 years ago

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