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

[Zael-Leonhart]

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] 

[Ahmedx]

I don’t understand what you did?

[Zael-Leonhart]

@Ahmedx 

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"

[Ahmedx]

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

[Zael-Leonhart]

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

[Ahmedx · Accepted solution]

ok? try this

[Zael-Leonhart]

Thanks for the time spent. Well appreciated. 

[Zael-Leonhart]

@Ahmedx 

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

[Ritaf1983]

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

all the companies are unique...

[Zael-Leonhart]

@Ritaf1983 unfortunately, rankx function wont work on directquery 😞

[Ritaf1983]

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

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

[Zael-Leonhart]

@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.

[Ritaf1983]

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

[Zael-Leonhart]

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

take a look

that's my only options

[danextian]

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]
)

[Zael-Leonhart]

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? 

[danextian]

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

[Zael-Leonhart]

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

here is my expected output..

[danextian]

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.

and to achieve 8, the formula is as below:

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

[Zael-Leonhart]

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

[danextian]

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

[Zael-Leonhart]

@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