cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
n123b Helper II
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

 

ezgif.com-gif-maker.jpg

 

Any advice?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
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

View solution in original post

9 REPLIES 9
Super User IV
Super User IV

Re: Percentage of total calculation

@n123b instead of image, can you share sample data with expected output. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490






Did I answer your question? Mark my post as a solution.

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





Super User II
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
LinkedIn

n123b Helper II
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
Super User II

Re: Percentage of total calculation

@n123b 

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
LinkedIn

Super User IV
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






Did I answer your question? Mark my post as a solution.

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





n123b Helper II
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:

ezgif.com-gif-maker.png

 

The visual is created using the FeatureProgress table:

 

ezgif.com-gif-maker (1).png

 

Which has the following relationship:

 

ezgif.com-gif-maker (2).png

 

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'

 

ezgif.com-gif-maker (3).png

 

Community Support
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
Helper II

Re: Percentage of total calculation

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

Community Support
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

View solution in original post

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