Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
raulopez
Frequent Visitor

Wrong total in power bi to columns

Hi friends!

I have a case in Power BI, that perhaps one of you has happened, I need that in the table type visualization the distitutive count is totaled by ID, the general total is calculated well, but when I add any of the fields of the table as client's office, the totals for sex, office, attention make a distinctive sum different from the total.

Example with filter in year 2021 and semester 2, with the fields sex, office, attention, when doing the summation by category it differs from the distinctive total.

unique = DISTINCTCOUNT ('TABLE' [ID])

There is also a field of dates that can be repeated in many records.
Example campos.png






11 REPLIES 11
Syndicate_Admin
Administrator
Administrator

I ran the example of @Syndicate_Admin as is and I have the required result, but now I must apply the same for the same column but with a different row level. Example
Rows:
Shop
hours
However the formula applied adds me correctly in the first level but in the 2nd level of row it is distorted.

v-jayw-msft
Community Support
Community Support

Hi @raulopez ,

 

It's hard to reproduce the scenario, can you share some sample data in text format instead of screenshot and share the formula you are using?

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

I attach the PBIX and the dataset, you will be able to observe the difference of the summations with the distittive total.

Download: https://github.com/orlapez/API

PaulDBrown
Community Champion
Community Champion

DISTINCTCOUNT returns the count of unique values in the filter context. For the total, you are seeing the count of unique values overall (taking into account any slicers applied). What is it you hope to see?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






My requirement is that the individual sums of each table correspond to the total, as can be seen in each table the total is 5356 when adding Female + Male, FILE + LIBRARY + WAREHOUSE + GYM or Advisory + claim + complain + conference + etc. the total does not coincide with the summations. and then if 5356 is given as a distinctive result in general, the sum should coincide with that total.

Thank you!

Taking this simple model as an example:

Model.JPGWith these two measures...

 

Distincount Version =
DISTINCTCOUNT ( Sales[Version] )
Distinctcount version SUMX =
SUMX (
    SUMMARIZE (
        'DIM Product',
        'DIM Product'[Product],
        "@count", DISTINCTCOUNT ( Sales[Version] )
    ),
    [@count]
)

 

 

you get this:

result.JPG

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi PaulDBrown,thanks!

I have only a table , without relationships. 

If you agree, I will share a pbix and the sources with the expected result, it seems that when removing duplicates from power bi, it is not applying it in the correct way.

 

Sure! Share the files using a cloud service such as OneDrive, Dropbox, Google Drive etc...





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi,

I do not see any mistake anywhere.  Even if you write this measure

=Measure = DISTINCTCOUNT(Example_catered[Cod])

you get the result as 5,356.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Ashish.  It is that the total is not incorrect, that the measurement is correct, it is about the individual values ​​when they are added, they must coincide with the same number of the total. Try adding them. the problem is applying the distinctive count to the dataset. I need to find the solution to get the distinctive values ​​without having to do it separately per semester.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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