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
Zael-Leonhart
Helper I
Helper I

Total of Distinct is correct but the displayed values of column is wrong

i have problem in my 2 columns an [area] and [count of companies] in that area.. I put them together in a table, then I added distinct to [count of companies]. Then this happened. the total of the column is correct but the displayed contents of each row of [company] when added manually it doesn't match with the total .. it is usually higher.

 

I would like to get the distinct values of each row when added up that matches with the total of distinct count and would like to make clear the total is correct but the total of each row values when added up will not match the total. I would like to match the row values to the total of distinct [count of company]

 

Can I ask for your kind guidance about this. Thank you.

 

my columns are

[company] [sales] [area] 

 

 

 

1 ACCEPTED SOLUTION

42 REPLIES 42

I don’t understand what you did?

 
RANK = RANKX(fILTER('Project v2','Project v2'[Company]=EARLIER('Project v2'[Company])),'Project v2'[AREA],,ASC,dENSE)
 
I don't know what went wrong, all in red is in error state. ="Parameter is not the correct type"

you want to create a measure, but it me it’s not a measure, but a calculated column

Screenshot_1.png

 

@Ahmedx reviewing some other post looks like the rankx can be used only as a measure.

ok? try this

 

Thanks for the time spent. Well appreciated. 

ZaelLeonhart_0-1701260890034.png

@Ahmedx 

unfortunately, it won't work.. really complicated direct query.. makes things difficult

If you change the logic of ranking to asc instead the desc you get:

the formula for ranking column : 

RANK = RANKX(fILTER('Table',[Company]=EARLIER([Company])),[LastDigitArea],,ASC,dENSE)
the measure 
count_companies = CALCULATE(DISTINCTCOUNT('Table'[Company]),'Table'[RANK]=1)+0

Ritaf1983_0-1701231670174.png

all the companies are unique...

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

@Ritaf1983 unfortunately, rankx function wont work on directquery 😞

 

ZaelLeonhart_1-1701261136307.png

 

Hi @Zael-Leonhart  you can achieve the same result of rankx with power query.

https://www.youtube.com/watch?v=ej60Wxaum_E

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

@Ritaf1983 I think excel power query is not applicable with me.. my data is coming from dynamics 365 field service and need the auto update shared dashboard and must use direct query. Please correct me if I'm wrong. Thanks.

@Zael-Leonhart 
You don't need Excel. 
You have power query in power bi

Ritaf1983_0-1701263486759.png

 

Regards,
Rita Fainshtein | Microsoft MVP
https://www.linkedin.com/in/rita-fainshtein/
Blog : https://www.madeiradata.com/profile/ritaf/profile

hmm.. it looks like it can't be applied in my case. 

 

take a look

 

ZaelLeonhart_0-1701264525914.png

that's my only options

 

danextian
Super User
Super User

Hi @Zael-Leonhart ,

 

It is a common scneario with using distinct. Is it evaluated  on a per row level in your visual but overall in the total.

In the example table below, Hello will have to distnct values while Hi has one. The overall total will have only 2 as that is distinct count over all.

Category To Count
Hello A
Hi A
Hello A
Hi B

 

The sample measure below will create a virtual table of distinct count by company and area and will add up the value for each company and area combination.

=
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( data, data[company], data[sales] ),
        "@count", [distinct count measure]
    ),
    [@count]
)









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


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Hello @danextian,

Thanks for the reply. Sorry I'm just starting in Power BI.. 

 

=
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( data, data[company], data[sales] ),
        "@count", [distinct count measure]
    ),
    [@count]
)

 sorry for the noob question.. the [distinct count measure].. what measure is involved here? 

Replace that with the measure you initially used, the one which the sum of rows does not match the total.










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


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

@danextian I tried your suggestion bro. but the output is no good. Sorry

here is my expected output..

 

 

ZaelLeonhart_0-1701162660410.png

 

Hi @Zael-Leonhart ,

 

Your expected result based on your sample data doesn't make sense to me. How did you come up with 2 for Area 2 when the distinct company count is 3? The same case with other areas.  Distinct company count for each should be: Area 2 = 3, Area 3 = 1 and Area 4 = 6 which totals 8.

danextian_0-1701165492121.png

and to achieve 8, the formula is as below:

count =
SUMX (
    ADDCOLUMNS (
        SUMMARIZE ( 'Table', 'Table'[Area] ),
        "@count", CALCULATE ( DISTINCTCOUNT ( 'Table'[Company] ) )
    ),
    [@count]
)

 










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


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Many are requesting the output 8.. Thanks for the reply bro. But, my case is unique. I'm requesting the total output 6 with per row sum equals to 6 too. The distinct output of power bi for me is correct and I want to the per row output to match the distinct displayed by power bi. Thanks

If so, please give the logic how to get 2, 0 and  4 and not 3, 1 and 4 respectively for each area.

 










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


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

@danextian Thanks for the reply, hmm the logic.. distinct vs unique.. the logic I want to point is the unique.. I hope you understand what I mean.. 

 

Company Sales Area

A 100 AREA 2 - counted

A 200 AREA 3 - not counted A is already at Area 2

C 400 AREA 4 - counted

B 100 AREA 4 - counted

B 200 AREA 4 - not counted B duplicate

B 400 AREA 2 - not counted have already B

H 100 AREA 4 - counted unique

I 200 AREA 4 - counted unique

J 400 AREA 2 - counted unique

 

 


I don't know if this will help.. if you are familiar with Qlik... i used the expression
sum(aggr(count(distinct [Company]),[Company])). I hope this helps. Thanks

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.