Filtering and counting the result of a measure.

[Anonymous]

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.

[Greg_Deckler]

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.

[Anonymous]

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

[Greg_Deckler · Accepted 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.

[Anonymous]

Hi @Greg_Deckler

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.

https://community.powerbi.com/t5/Desktop/Measure-to-know-the-total-percentage-by-category/m-p/419310...

 
Do you have any other idea how I can do this?

[Anonymous]

Count FALSE:= COUNTROWS(FILTER(DISTINCT(Filiais[SiglaFiliais]);[Deviation]="FALSE"))

Count TRUE:= COUNTROWS(FILTER(DISTINCT(branches[abbrevbranches]);[Deviation]="TRUE"))