cancel
Showing results for
Did you mean:
Highlighted
n123b Helper II

## Percentage of total calculation

Hey Guys,

Need help with trying to show percentage completion.

I've got the following data and I want to create a measure so that I can have a bar chart to show % of total depending on the Descendants.State 1 ACCEPTED SOLUTION

Accepted Solutions Community Support

## Re: Percentage of total calculation

Hi @n123b ,

For dynamic change display value type, you can add a selector table with two types, then add if statement to compare with selection and return corresponding expression result:

``````Measure =
IF (
SELECTEDVALUE ( Selector[Type] ) = "Percent",
DIVIDE (
CALCULATE (
SUM ( Table[Amount] ),
ALLSELECTED ( Table ),
VALUES ( Table[Category] ),
VALUES ( Table[Legend] )
),
CALCULATE (
SUM ( Table[Amount] ),
ALLSELECTED ( Table ),
VALUES ( Table[Category] )
)
),
CALCULATE (
SUM ( Table[Amount] ),
ALLSELECTED ( Table ),
VALUES ( Table[Category] ),
VALUES ( Table[Legend] )
)
)
``````

Notice: 'category' is axis, 'legend' means legend field, 'amount' is value fields.
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
9 REPLIES 9 Super User IV

## Re: Percentage of total calculation

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.  Super User II

## Re: Percentage of total calculation

hi @n123b

did you try a simple measure

``````Measure = divide(
calculate(sum(FeatureProgress[Descendants.Count]), ALLEXCEPT(FeatureProgress, FeatureProgress[Descendants.State])),
calculate(sum(FeatureProgress[Descendants.Count]), ALL(FeatureProgress))
)``````

?

do not hesitate to give a kudo to useful posts and mark solutions as solution

do not hesitate to give a kudo to useful posts and mark solutions as solution

n123b Helper II

## Re: Percentage of total calculation

@az38 thanks for that - works for me 🙂

Only thing is - that Measure seems to ignore all my page filters - should that be expected? 😕 Super User II

## Re: Percentage of total calculation

yes, because of usage ALL and ALLEXCEPT functions

you can try use something like

``````Measure = divide(
calculate(sum(FeatureProgress[Descendants.Count]), FILTER(FeatureProgress, FeatureProgress[Descendants.State]=SELECTEDVALUE(FeatureProgress[Descendants.State]))),
calculate(sum(FeatureProgress[Descendants.Count]))
)``````

but this behaviour will depend on exactly data model and slicer usage

do not hesitate to give a kudo to useful posts and mark solutions as solution

do not hesitate to give a kudo to useful posts and mark solutions as solution Super User IV

## Re: Percentage of total calculation

@n123b and that was the reason on my original reply mentioned that it is import to know how you data model looks like, sometime just creating a measure is not the solution but need to know more about data model and how you are visualizing the data. FYI

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs. n123b Helper II

## Re: Percentage of total calculation

Ah gotcha, so to elaborate:

There is an Areas table which I'm using as a filter on all pages, using the AreaPath column: The visual is created using the FeatureProgress table: Which has the following relationship: I'm trying to create a visual on top of the current visual I have in Power BI, which just shows count of child items in a particular state. I want to have a button to flip between 'View as Count' / 'View as Percentage'  Community Support

## Re: Percentage of total calculation

HI @n123b ,

If you want to keep the original filter effect in measure formula, I'd like to suggest you to use allselected to replace all function:

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT

In addition, you can refer to the below blog to know more about corresponding calculation with hierarchy level fields:

Use IsInScope to get the right hierarchy level in DAX

BTW, current you can't create a dynamic calculate column/table based on filter/slicers.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
n123b Helper II

## Re: Percentage of total calculation

Thanks - but I'm still unsure on what my measure should be? Community Support

## Re: Percentage of total calculation

Hi @n123b ,

For dynamic change display value type, you can add a selector table with two types, then add if statement to compare with selection and return corresponding expression result:

``````Measure =
IF (
SELECTEDVALUE ( Selector[Type] ) = "Percent",
DIVIDE (
CALCULATE (
SUM ( Table[Amount] ),
ALLSELECTED ( Table ),
VALUES ( Table[Category] ),
VALUES ( Table[Legend] )
),
CALCULATE (
SUM ( Table[Amount] ),
ALLSELECTED ( Table ),
VALUES ( Table[Category] )
)
),
CALCULATE (
SUM ( Table[Amount] ),
ALLSELECTED ( Table ),
VALUES ( Table[Category] ),
VALUES ( Table[Legend] )
)
)
``````

Notice: 'category' is axis, 'legend' means legend field, 'amount' is value fields.
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI

Announcements #### 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!

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

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
Users online (2,413)