cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kamsingh11
Helper IV
Helper IV

Incorrect Row Totals for Cumulative Sales

Hello!

 

I have a table where each row is already having cumulative sales in it and I have created a measure that displays cumulative sales of each model per part here as follows:

Cum_Sales =
      Var Max_Date = MAX(Model_Sales[Date])
      Var Latest_Sales = CALCULATE(SUM(Model_Sales[Cumulative Model Sales]), Model_Sales[Date] = Max_Date)
      Return Latest_Sales
 
My cumulative sales for each model per part is all correct as is supposed to be. However, my totals of cumulative sales of all models per part is not coming accurate for row totals. When I try to see totals for any specific Part Number, then the total in row is either same as the cumulative sales of a speicifc Model number or is close to totals of all model numbers or is right total of all models. Is there anything that needs to be changed in the DAX shown above for cumulative sales while I am trying to see my cumulative sales of each model per part so that my Totals are correct?

 

I would like to see the Totals as total of cumulative sales of each Model for any selected Part. Totals of column is showing the right totals as that is the cumulative sales per part.  I have tried to create a mini sample to replicate the issue.

 

In below example shows incorrect totals of 1900 for 2022 & it should be 2320 instead.Its taking the value of only one model. Column totals are correct.

kamsingh11_0-1655663357214.png

.pbx file Link: https://drive.google.com/file/d/191yFa8i6Pp--XxbF7iVN0QeRX3dGROKE/view?usp=sharing

 

In my real data, we have same issue as above. But there are also some rows with totals close to right total, but not exact right total. For example, if it is supposed be 1426 in above example for 2021, it shows 1418 or so. There are very few rows with right totals. 

 

Thanks for your help!

 

 

 

 

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @kamsingh11 ,

 

Try this:

Cum_Sales_Tot V3 =
IF (
    HASONEVALUE ( Dates[Year] ) || HASONEVALUE ( Part_to_Model[Model] ),
    [Cum_Sales_Tot V2],
    MAXX (
        TOPN (
            1,
            SUMMARIZE ( Dates, Dates[Year], "Cum_Sales_V3", [Cum_Sales_Tot V2] ),
            [Year]
        ),
        [Cum_Sales_V3]
    )
)

Icey_0-1656054119289.png

 

 

Best Regards,

Icey

 

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

View solution in original post

7 REPLIES 7
AlB
Super User
Super User

@kamsingh11 

Cum_Sales_Tot V2 = 
IF(ISINSCOPE(Part_to_Model[Model]),
   [Cum_Sales],
   SUMX(CROSSJOIN(DISTINCT(Dates[Year]), DISTINCT(Part_to_Model[Model])), [Cum_Sales]))

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Thanks @AlB !

 

It works great for Row Total per Year as well as Column Total per Model except for the Total in the intersection of Row and Column. So, instead of 920 for our example, it should be 420

kamsingh11_0-1655748616757.png

Thanks again for your help!

 

 

Please help to fix this last piece of puzzle.

Thanks a bunch in advance!

Icey
Community Support
Community Support

Hi @kamsingh11 ,

 

Try this:

Cum_Sales_Tot V3 =
IF (
    HASONEVALUE ( Dates[Year] ) || HASONEVALUE ( Part_to_Model[Model] ),
    [Cum_Sales_Tot V2],
    MAXX (
        TOPN (
            1,
            SUMMARIZE ( Dates, Dates[Year], "Cum_Sales_V3", [Cum_Sales_Tot V2] ),
            [Year]
        ),
        [Cum_Sales_V3]
    )
)

Icey_0-1656054119289.png

 

 

Best Regards,

Icey

 

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

Thanks @Icey! It works perfectly!

Thank you so much!

 

Thanks @AlB for making it simpler throughout and for solving it 95% for us.

 

Thank you both!

AlB
Super User
Super User

Hi @kamsingh11 

Create a new measure based on [Sum_Sales] you already have:

Cum_Sales_Tot = 
SUMX(CROSSJOIN(DISTINCT(Dates[Year]), DISTINCT(Part_to_Model[Model])), [Cum_Sales])

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Thanks @AlB!

 

Row Totals are now correct and thats what I am looking for. However, the Column total was correct earlier showing only cumulative total per Model. Now, it shows column total as cumulative for all the years per model  instead of showing totals for only 2022 as that is cumulative value so far per model. How can we modify DAX and also, if this can be applicable even if months are selected as data is cumulative for each month.

kamsingh11_0-1655695768580.png

 

Thanks again for your help!

 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors