Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.