cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ralsouza Regular Visitor
Regular Visitor

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

Re: Filtering and counting the result of a measure.

@ralsouza- 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
Super User

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!

Highlighted
ralsouza Regular Visitor
Regular Visitor

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

Re: Filtering and counting the result of a measure.

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

ralsouza Regular Visitor
Regular Visitor

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?

ralsouza Regular Visitor
Regular Visitor

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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 374 members 3,682 guests
Please welcome our newest community members: