Forum Discussion

CarlsBerg999's avatar
CarlsBerg999
Helper 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 calculate the sum of quotes, if the Quoted-column has a date value. 

Data is set so that each Quarter has 100 000€ in sales quotes. Therefore the total is 100 000€ + 100 000€ + 100 000€ = 300 000€. 

> In the table below, i have filtered out Qtr1 (100 000€)

>> I want the grand total to be 200 000€ in the pivot table below. However it calculates the Qtr1 to the Grand total.

 

Row labelsSum of Total value
2020 
   Qtr2100 000€
   Qtr3100 000€
Grand Total300 000€

 

What am i doing wrong? I have taken away the "mark" from pivot table options that says "Include filtered items in totals", but this does not change the Grand total.

 

 

Need Help

4 Replies

    • CarlsBerg999's avatar
      CarlsBerg999
      Helper 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
        Community 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
    )
)

         

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    hi  CarlsBerg999 

    This looks like a measure totals problem. Very common. See this post about it
    https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

    Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
    https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

     

    If you still have the problem, please share your sample pbix file for us have a test.

     

    Regards,

    Lin