cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Measure to know the total percentage by category

I have two tables in SSAS that have one column for Budget, another table with Accomplished, and another table that has the list of branches.

I need to create a measure that aggregates the sum for each branch, compare if the goal has been exceeded, if so, check TRUE if not as FALSE.

 

Capturar.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Measure to know the total percentage by category

Hi
 
1.First create a measure that tells which subsidiaries did not reach the goal
 
budget sum = CALCULATE(SUM(Table13[budget]),ALLEXCEPT(Table13,Table13[branch]))
accomplish sum = CALCULATE(SUM(Table13[accomplish]),ALLEXCEPT(Table13,Table13[branch]))
Measure 3 = IF([budget sum]>[accomplish sum],"True","Flase")
 
2.Calculate the percentage of how many could not reach the target against the total number of branches.
 
Does the row with “true” mean not reaching the target, if so,
Total true = CALCULATE(DISTINCTCOUNT(Table13[branch]),FILTER(ALL(Table13),[Measure 3]="True"))
Total flase = CALCULATE(DISTINCTCOUNT(Table13[branch]),FILTER(ALL(Table13),[Measure 3]="Flase"))
the total number of branches = CALCULATE(DISTINCTCOUNT(Table13[branch]),ALL(Table13))
percentage = DIVIDE([Total true],[the total number of branches])
5.png
3.have a gauge graph
add measure “the total number of branches” to Max field, “Total true” to Pointer value field, “percentage” to Percentage field.
6.png
 
Best Regards
Maggie

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: Measure to know the total percentage by category

Hi
 
1.First create a measure that tells which subsidiaries did not reach the goal
 
budget sum = CALCULATE(SUM(Table13[budget]),ALLEXCEPT(Table13,Table13[branch]))
accomplish sum = CALCULATE(SUM(Table13[accomplish]),ALLEXCEPT(Table13,Table13[branch]))
Measure 3 = IF([budget sum]>[accomplish sum],"True","Flase")
 
2.Calculate the percentage of how many could not reach the target against the total number of branches.
 
Does the row with “true” mean not reaching the target, if so,
Total true = CALCULATE(DISTINCTCOUNT(Table13[branch]),FILTER(ALL(Table13),[Measure 3]="True"))
Total flase = CALCULATE(DISTINCTCOUNT(Table13[branch]),FILTER(ALL(Table13),[Measure 3]="Flase"))
the total number of branches = CALCULATE(DISTINCTCOUNT(Table13[branch]),ALL(Table13))
percentage = DIVIDE([Total true],[the total number of branches])
5.png
3.have a gauge graph
add measure “the total number of branches” to Max field, “Total true” to Pointer value field, “percentage” to Percentage field.
6.png
 
Best Regards
Maggie

View solution in original post

Anonymous
Not applicable

Re: Measure to know the total percentage by category

Hi @v-juanli-msft, this solution is great! But I'm having trouble to count the booleans.

 Capturar.PNG

 

It may have something to do because the budgets, accomplish, and branch tables are separate in the data model.

 

Diagram.PNG

My formulas:

 

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

 

Anonymous
Not applicable

Re: Measure to know the total percentage by category

Finally I did, it was necessary to change two more formulas:

 

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.

Top Solution Authors
Top Kudoed Authors