cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Dealing with missing rows in underlying data, and adding 0 values

To put it simply our database is calculating 40 metrics for thousands of projects on a daily basis. Each row in the database has project ID, metric type, value, and date. To save on performance our database doesn't store rows if the metric calculated is 0, however, the metric being 0 is actually an important insight, and something we want to display in reports. 

 

The issue I'm having is that I'm unable to satisfactorily display the table visual, and specifically the totals row. I can use a measure to display 0 if the metric is missing, no problem. However, a really important piece of these metrics is displaying their average score across the projects which I use the totals row for, and this is where the crux of the issue lies.

 

If I use a measure to display 0, the average won't take into account those 0's added by the measure and simply display an average of the metrics which have values >0.

 

Would really really love some help on this, and you'd be my hero!

 

I have created these three simplified tables below to give you better feel for the issue. 

1. First table shows which data is stored in the database

2. Second table shows how I want to display data in report view

3. Third table shows how I have tried to solve the problem unsucccesfully

 

How underlying data comes in

Project IDMetricDate
110May
3123May
512May
662May
115June
260June
523June
613June

 

How I want to display data with the average taking into account zeros

Project IDMetric
112.5
230
361.5
40

5

17.5
637.5
Avg26.5

 

 

How I have been displaying data with wrong average

Project IDMetric
112.5
260
3123
40 (added by measure)

5

17.5
637.5
Avg39.75

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

15 REPLIES 15
Greg_Deckler
Super User
Super User

@Anonymous - OK, what column are you using in the axis of your Line chart then?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

I'm using the date column, more specifically the date hierarchy with months, and years. 

@Anonymous - OK, can you post the measure formulas that we created? I should be able to tell what is going haywire if I have those.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Sure thing!

 

Measure 13 = var testmeasure1 = AVERAGE('CSDB project_metric'[Work at Height])
RETURN
IF(ISBLANK(testmeasure1),0,testmeasure1)
 
Measure 14 =
VAR TABLE1 = SUMMARIZE('CSDB project','CSDB project'[id],"Work at height test",[Measure 13])
return
if(HASONEVALUE('CSDB project'[id]),[Measure 13],AVERAGEX(TABLE1,[Work at height test]))
Anonymous
Not applicable

@Greg_Deckler 

 

Any luck with this? Is there anything else I can provide?

 

Again, your help is greatly, greatly appreciated. 

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@Greg_Deckler 

 

Greg thanks for your excellent help here, you have truly solved the issue as outlined in the post. For anyone reading the solution lies in Greg's link https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

 

However, I have run into a bit of a complication that I haven't been able to crack. Creating the measure where you create a synthetic table, and use the averages for the rows of that table works well for the table visual. However, it doesn't seem to work for the card visual or the line chart visual. 

 

Do you have any idea why that could be?

@Anonymous - What is the axis of your Line chart? Can you use the m_single version of the measure instead of the m_total version of the measure?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

I tried the m_single version of the measure and it returns the wrong average. It actually returns the same average as the default row totals average does. So it is not taking into account the rows with a zero value in the average calculation. 

Anonymous
Not applicable

Okay I'll try that out!

 

I have date as the x-axis

mahoneypat
Microsoft
Microsoft

Have you tried this approach?

 

Project Avg = AVERAGEX(VALUES(Table[Project ID]), [Metric])

 

If your metric measure returns zero, it would be included in the average.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

I have tried that approach, and it works on the project row level. But it doesn't produce the desired result for the totals row (where we want an average of averages)

lbendlin
Super User
Super User

You need a master table for your projects

 

Annotation 2020-07-20 105141.png

 

After that it is trivial.  In your visual select the project ID from the master table and the metric from the results table. Show items wth no data.

 

And the measure will be 

 

Measure = divide(sum(Results[Metric]),COUNTROWS(Projects))

 

Anonymous
Not applicable

Thanks for your reply.

 

So I have a master table for my projects, but I realize there is perhaps some more complexity I need to add in this for you.

 

In the table visual I'm looking at the average of the project metric over time for each individual project, and I have a filter to select time in the report. So the table I shared with you earlier with the metrics, each row there is an average over time for a particular project.  I have built a filter so the user can select time frame. Some projects will have data for the entire time frame, while others will not have data for the entire time, so therefore I believe I can't simply divide the sum of the metric by the project count.

 

 

 

 

 

Anonymous
Not applicable

I have updated the tables in my OP to better explain the complexity

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors