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.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
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.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
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.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

View solution in original post

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?

Highlighted
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
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors