cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
poojithas
Helper III
Helper III

Incorrect Totals when using measure in table

Hi All,

I am using a measure with the below formula in my table.

poojithas_0-1681790316630.png

When I use this measure in a table, I am getting incorrect totals. The expected total is 1164 but I get 1160.

I tried using a calculated column instead of a calculated measure, but the results are still inacurate.

poojithas_1-1681790486658.png

Could someone please assist on this.

Thank you,

Poojitha

 

2 ACCEPTED SOLUTIONS

Hi @poojithas ,

 

When it comes to non-additive measures (DISTINCTCOUNT is one of them) there is no easy one solution fits all approach.

 

I recommend you the following post by sqlbi. There is loads to take away from it:

https://www.sqlbi.com/articles/why-power-bi-totals-might-seem-inaccurate/

 

Hope this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

poojithas
Helper III
Helper III

This is fixed using the below logic:
=sumx(values(locn),measure2)

View solution in original post

4 REPLIES 4
poojithas
Helper III
Helper III

This is fixed using the below logic:
=sumx(values(locn),measure2)

tackytechtom
Super User
Super User

Hi @poojithas ,

 

Here a an example which might give you an idea on why your total differs from the expected outcome:

 

tackytechtom_0-1681791443380.png

 

On the left, a table where each block has a different value. In total there are 4 different values (1, 2, 3, 4), but when grouping by block and using a DISTINCTCOUNT on "Value", we can see that each block might have a different number of distinct values, i.e. California has 3 (1, 2, 3) while Florida has 2 (1, 2). The total itself does not sum over what is shown above in the table above but instead calculates the Measure without applying a filter. This means it total just returns all distinct values, that is 4 (1, 2, 3, 4).

 

Let me know if this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@tackytechtom   - Thank you for the details provided on why the totals are differing.

Would like to check if there is a way we can show the expected totals and does not return the distinct values.

Hi @poojithas ,

 

When it comes to non-additive measures (DISTINCTCOUNT is one of them) there is no easy one solution fits all approach.

 

I recommend you the following post by sqlbi. There is loads to take away from it:

https://www.sqlbi.com/articles/why-power-bi-totals-might-seem-inaccurate/

 

Hope this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

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

Top Solution Authors
Top Kudoed Authors