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

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 33 members 875 guests
Please welcome our newest community members: