cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

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

Best Regards
Maggie
3 REPLIES 3
Community Support Team

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

Best Regards
Maggie
Regular Visitor

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

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

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

Regular Visitor

## 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"))```

Announcements

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 273 members 2,749 guests
Recent signins: