Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey
Hey My grand total is wrong when using distinct count, if the visualisation is a matrix, table or any. The grand totalt is wrong. my dax formel is
User Distinct = DISTINCTCOUNT(table[UserId] )
There are many users who do different operations, one user can do a operation 4-5 times and etc.. but i want to have it distinct. But the sum when i filter it with a slicer or any other thing, it shows the row value correct, but the grand totalt is wrong!
NEED HELP ASAP!
thanx in advance,
Solved! Go to Solution.
Hi @Anonymous,
In your scenario, the measure will evaluate who column [UserId] and count distinct values on Total row of the table visual, instead of sum each measure row values.
If you would like to place total of each measure value on Total row, you can modify the measure below:
User Distinct = var t=SUMMARIZE('Table5','Table5'[Operation],"UserDistinct",DISTINCTCOUNT(Table5[UserID] ))
return
SUMX(t,[UserDistinct])
Best Regards,
Qiuyun Yu
Hi @Anonymous,
In your scenario, the measure will evaluate who column [UserId] and count distinct values on Total row of the table visual, instead of sum each measure row values.
If you would like to place total of each measure value on Total row, you can modify the measure below:
User Distinct = var t=SUMMARIZE('Table5','Table5'[Operation],"UserDistinct",DISTINCTCOUNT(Table5[UserID] ))
return
SUMX(t,[UserDistinct])
Best Regards,
Qiuyun Yu
Hi @v-qiuyu-msft ,
If suppose I want overall disticount Not sum of user id distinctcount then that time what should I do?
Thanks,
Arvind
Hi @v-qiuyu-msft ,
If we include another column lets say 'Operation Category' then in the below query I have to replace the 'Operation' with 'Operation category' level table.
So, instead for changing this query again & again, Is there anyway it can calculate the distinct count according to the columns('Operation' or 'Operation category') we use in the table & the Grand total row sum it accordingly ?
User Distinct = var t=SUMMARIZE('Table5','Table5'[Operation],"UserDistinct",DISTINCTCOUNT(Table5[UserID] ))
return
SUMX(t,[UserDistinct])
Hi, how would a measure that calculates the % of the Total (Operation 1 = 25%, 5=12,5%) look like?
Best regards,
Chris
Hi There!
I have to do the same but filter it between two dates using slicers as my filters
for an e.g. count of distinct operations but for last week and previous last week and cannot apply the filter on the summarization though.
@Anonymous
Could you provide help on this https://community.powerbi.com/t5/Desktop/Distinct-Count-measure-Grand-Total-is-showing-Wrong-Value/m-p/716742#M345891
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |