Forum Discussion

mweber's avatar
mweber
Frequent Visitor
6 years ago

Measure Subtotal Not Working

I'm trying to create a visual where a user can quickly look at year-over-year sales comparisons

 

My current visual: column 1 = date, column 2 = sales for selected date range (in 2019) & column 3 = sales from same period last year.

 

Column 2 measure = CALCULATE(SUM('Weekly Sales'[Sales]),FILTER('Weekly Sales','Weekly Sales'[InvoiceDate]))

Column 3 measure = CALCULATE(SUM('Weekly Sales'[Sales]),SAMEPERIODLASTYEAR('Calendar (2)'[Date]))

A relationship between Calendar (2) & Invoice date has been created.

 

Column 3 is yeiding the results for a specific date as one would expect. However, the subtotal on the visual is not correct. I have been trying HASONEFILTER & HASONEVALUE with no success. Any help would be appreciated!

 

 

Need Help

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    This looks like a measure totals problem. Very common. See my post about it here: 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

     

    Also, look for Matrix Measure Triple Threat Rock & Roll in the Quick Measures gallery, provides even more guidance on things like this.

    • mweber's avatar
      mweber
      Frequent Visitor
      6 years ago

      Thanks for the reference but still not getting there... tried this formula and still getting wrong totals:

       

      Measure - = if(HASONEFILTER('Calendar (2)'[Date]),CALCULATE(SUM('Weekly Sales'[Sales]),SAMEPERIODLASTYEAR('Calendar (2)'[Date])),CALCULATE(SUMX('Weekly Sales','Weekly Sales'[Sales]),FILTER('Calendar (2)',SAMEPERIODLASTYEAR('Calendar (2)'[Date]))))
       
       
       
      • dax's avatar
        dax
        Community Support
        6 years ago

        Hi mweber, 

        If possible, could you please inform me more detailed information(such as your sample data and your expected output or your pbix  file)? Then I will help you more correctly.

        Please do mask sensitive data before uploading.

        Thanks for your understanding and support.
        Best Regards,
        Zoe Zhi

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.