Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
I have a problem with Power BI not displaying values correctly when they are calculated as percentage of grand total.
The dataset has an ID colum with four values (A, B, C and D), there is also three more columns: year, month and type. This three column are used as slicers.
I want to calculate the number of values for every id, for this i have a matrix:
The matrix shows the number of values for every id, after apply the slicers, the matrix also shows the percent of the column, this was calculate using:
percent = count(Test[ID]) / CALCULATE(count(test[id]);all(Test[ID])).
The problem i have is when use "Id" as slicer and choose for example "A" the number stays the same but the perncet change.
How do I fix this?
Thanks.
Solved! Go to Solution.
hi @Anonymous
Basically, they all in one table, preserving filter context on [ID] indirectly preserves filter context on [MONTH] and also [YEAR]/[TYPE], which is not what you want.
So adjust the model as below:
Step1:
Create separate dim ID table and create a relatinship with basic data table
and then use it in table visual and slicer
Step2:
Then adjust the formula as below:
By the way, for dim fields you'd better use a dim table and create the relationship with fact table.
Regards,
Lin
Hi @Anonymous
In your formula, instead of using ALL use ALLSELECTED
Let me know if that works.
---
Please hit the "Accept as Solution" button if my post answered your question! If my post was helpful please consider giving it a "Thumbs Up."
Hi @Anonymous, thanx for your reply.
I tried with ALLSELECTED and didn´t work, i also tried with the formula:
percent = count(Test[ID]) / CALCULATE( COUNT(Accidentes[HORARIO]); ALLEXCEPT(Test; Test[year]; Test[month]; Test[Type]))
The values of the percent still change.
hi @Anonymous
Basically, they all in one table, preserving filter context on [ID] indirectly preserves filter context on [MONTH] and also [YEAR]/[TYPE], which is not what you want.
So adjust the model as below:
Step1:
Create separate dim ID table and create a relatinship with basic data table
and then use it in table visual and slicer
Step2:
Then adjust the formula as below:
By the way, for dim fields you'd better use a dim table and create the relationship with fact table.
Regards,
Lin
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |