Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I need to count the number of times TRUE and FALSE results appear to have a percentage value at the end.
For some reason the Total True and Total False measurements are not counting correctly the Deviation column.
In the end I need to create a gauge that shows the percentage of how many branches did not exceed the budget.
The model of the tables involved is as follows:
The measured columns are as follows:
Sum Budget:= CALCULATE(SUM(Budget[Budget]);ALLEXCEPT(Branches;Branches[abbrevbranches]))
Sum Accomplish:=CALCULATE(SUM(Accomplished[Accomplished]);ALLEXCEPT(Branches;Branches[abbrevbranches]))
Deviation:=IF([Sum Budget] > [Sum Accomplish];"TRUE";"FALSE")
Total True:=CALCULATE(DISTINCTCOUNT(Branches[abbrevbranches]);FILTER(ALL(Accomplished);Accomplished[Deviation]="TRUE"))
Total False:=CALCULATE(DISTINCTCOUNT(Branches[abbrevbranches]);FILTER(ALL(Accomplished);Accomplished[Deviation]="FALSE"))
Total branches:=CALCULATE(DISTINCTCOUNT(Branches[abbrevbranches]);ALL(Accomplished))
Percentage:=DIVIDE([Total True];[Total branches])
Can someone help me please? I have to deliver this work on Monday and I do not know what else to do.
Solved! Go to Solution.
@Anonymous- Take a look at the attached PBIX and see if it gets you there. The only thing it doesn't implement is the ALLEXCEPT for the abbreviations, I'll take a look at that if you can't get there from what I have attached.
Can you post that data in a form that can be copied and pasted instead of an image? Also, are they measures or calculated columns? There is no such thing as a measured column.
Sure!
They are all measures that are in a table in the SSAS.
I do not know if the fact that tables are separated in the data model can influence the generation of results.
The table's data in PowerBI are:
abbrevbranches | Budget | Accomplish | Sum Budget | Sum Accomplish | Deviation | Total True | Total False | Total branches | Percentage |
A | R$ 192.190,22 | R$ 196.867,96 | 19.219.022.005 | 196867.96 | FALSE | 13 | 13 | 13 | 1 |
B | R$ 733.151,04 | R$ 734.401,14 | 7.331.510.402.099.990 | 734401.14 | FALSE | 13 | 13 | 13 | 1 |
C | R$ 4.727.868,30 | R$ 4.984.209,75 | 4.727.868.300.719.990 | 4984209.75 | FALSE | 13 | 13 | 13 | 1 |
D | R$ 1.072.168,19 | R$ 1.249.844,00 | 107.216.818.563 | 1249844 | FALSE | 13 | 13 | 13 | 1 |
E | R$ 838.614,69 | R$ 993.508,41 | 8.386.146.873.999.990 | 993508.41 | FALSE | 13 | 13 | 13 | 1 |
F | R$ 802.908,45 | R$ 945.338,66 | 8.029.084.473.600.000 | 945338.66 | FALSE | 13 | 13 | 13 | 1 |
G | R$ 941.630,14 | R$ 1.033.401,67 | 9.416.301.385.099.990 | 1033401.67 | FALSE | 13 | 13 | 13 | 1 |
H | R$ 103.282,71 | R$ 28.155,71 | 10.328.271.363.000.000 | 28155.71 | TRUE | 13 | 13 | 13 | 1 |
I | R$ 320.137,16 | R$ 325.881,60 | 3.201.371.573.400.000 | 325881.6 | FALSE | 13 | 13 | 13 | 1 |
J | R$ 293.110,71 | R$ 298.311,55 | 29.311.071.272.999.900 | 298311.55 | FALSE | 13 | 13 | 13 | 1 |
L | R$ 1.423.564,38 | R$ 918.629,45 | 14.235.643.818.700.000 | 918629.45 | TRUE | 13 | 13 | 13 | 1 |
M | R$ 698.510,84 | R$ 703.079,78 | 6.985.108.438.000.000 | 703079.78 | FALSE | 13 | 13 | 13 | 1 |
N | R$ 313.868,44 | R$ 332.411,51 | 3.138.684.398.199.990 | 332411.51 | FALSE | 13 | 13 | 13 | 1 |
@Anonymous- Take a look at the attached PBIX and see if it gets you there. The only thing it doesn't implement is the ALLEXCEPT for the abbreviations, I'll take a look at that if you can't get there from what I have attached.
I did the same steps that you do not have a file, but it still did not work ... It does not make sense.
At the URL below is my original post where I described what I need to do.
Do you have any other idea how I can do this?
Count FALSE:= COUNTROWS(FILTER(DISTINCT(Filiais[SiglaFiliais]);[Deviation]="FALSE")) Count TRUE:= COUNTROWS(FILTER(DISTINCT(branches[abbrevbranches]);[Deviation]="TRUE"))