Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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]
Solved! Go to Solution.
I don’t understand what you did?
you want to create a measure, but it me it’s not a measure, but a calculated column
@Ahmedx reviewing some other post looks like the rankx can be used only as a measure.
Thanks for the time spent. Well appreciated.
If you change the logic of ranking to asc instead the desc you get:
the formula for ranking column :
all the companies are unique...
Hi @Zael-Leonhart you can achieve the same result of rankx with power query.
https://www.youtube.com/watch?v=ej60Wxaum_E
@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
hmm.. it looks like it can't be applied in my case.
take a look
that's my only options
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]
)
Proud to be a Super User!
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.
Proud to be a Super User!
@danextian I tried your suggestion bro. but the output is no good. Sorry
here is my expected output..
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]
)
Proud to be a Super User!
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.
Proud to be a Super User!
@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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |