cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

 

Capturar.PNG

 

The model of the tables involved is as follows:

Diagram.PNG

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

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.

 

ssas.PNG

 

The table's data in PowerBI are:

 

abbrevbranchesBudgetAccomplishSum BudgetSum AccomplishDeviationTotal TrueTotal FalseTotal branchesPercentage
AR$ 192.190,22R$ 196.867,9619.219.022.005196867.96FALSE1313131
BR$ 733.151,04R$ 734.401,147.331.510.402.099.990734401.14FALSE1313131
CR$ 4.727.868,30R$ 4.984.209,754.727.868.300.719.9904984209.75FALSE1313131
DR$ 1.072.168,19R$ 1.249.844,00107.216.818.5631249844FALSE1313131
ER$ 838.614,69R$ 993.508,418.386.146.873.999.990993508.41FALSE1313131
FR$ 802.908,45R$ 945.338,668.029.084.473.600.000945338.66FALSE1313131
GR$ 941.630,14R$ 1.033.401,679.416.301.385.099.9901033401.67FALSE1313131
HR$ 103.282,71R$ 28.155,7110.328.271.363.000.00028155.71TRUE1313131
IR$ 320.137,16R$ 325.881,603.201.371.573.400.000325881.6FALSE1313131
JR$ 293.110,71R$ 298.311,5529.311.071.272.999.900298311.55FALSE1313131
LR$ 1.423.564,38R$ 918.629,4514.235.643.818.700.000918629.45TRUE1313131
MR$ 698.510,84R$ 703.079,786.985.108.438.000.000703079.78FALSE1313131
NR$ 313.868,44R$ 332.411,513.138.684.398.199.990332411.51FALSE1313131
Super User IV
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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Filtering and counting the result of a measure.

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
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"))

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

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?

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.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors