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

Incorrect totals on a table for MTD calculations

Hi Power BI community,

 

I'm trying to calculate MTD totals for two factories. Here's some sample data.

 

PlantData

DatePlantVolume
01-11-2020A2
01-11-2020B1
02-11-2020A2
03-11-2020A2

 

If I don't filter by Plant, when I create a table for a MTD calculation for November 3, I get this result:

 

Incorrect Result

DatePlantMTD
03-11-2020A6
Total 7

 

The issue is that the Table Total is including data from Plant B, even though there is no volume data for Plant B on November 3. What I'd like to see is this:

Correct Result

DatePlantMTD
03-11-2020A6
Total 6

 

Here's the DAX I'm using to calculate the MTD:

 

MTD = TOTALMTD(SUM('PlantData'[Volume]), 'PlantData'[Date])

 

What am I doing wrong? How can I fix this? I've tried a couple different solutions, and none have worked. Here are the solutions' links:

 

Solved: Table subtotal not excluding blank values - Microsoft Power BI Community

Measure Totals, The Final Word - Microsoft Power BI Community

 

Now I may not be applying the solutions correctly, but I don't think that's the case

 

Thanks for any help.

 

Sincerely,

 

Travis

1 ACCEPTED SOLUTION
Community Support
Community Support

Hi @TReynolds ,

You can create another measure as below to replace the previous measure [MTD]:

Measure = SUMX(VALUES('PlantData'[Date]),SUMX(VALUES('PlantData'[Plant]),[MTD]))

Incorrect totals on a table for MTD calculations.JPGBest Regards

Community Support Team _ Rena
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

1 REPLY 1
Community Support
Community Support

Hi @TReynolds ,

You can create another measure as below to replace the previous measure [MTD]:

Measure = SUMX(VALUES('PlantData'[Date]),SUMX(VALUES('PlantData'[Plant]),[MTD]))

Incorrect totals on a table for MTD calculations.JPGBest Regards

Community Support Team _ Rena
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

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors