cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Filtering and counting the result of a measure.

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV

## Re: Filtering and counting the result of a measure.

@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 have book! Learn Power BI from Packt

Proud to be a Datanaut!

5 REPLIES 5
Super User IV

## Re: Filtering and counting the result of a measure.

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.

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Highlighted
Anonymous
Not applicable

## Re: Filtering and counting the result of a measure.

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
Super User IV

## Re: Filtering and counting the result of a measure.

@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 have book! Learn Power BI from Packt

Proud to be a Datanaut!

Anonymous
Not applicable

## Re: Filtering and counting the result of a measure.

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
Not applicable

## Re: Filtering and counting the result of a measure.

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

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

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors