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

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

Hi @Anonymous ,

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
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

9 REPLIES 9
az38
Community Champion
Community Champion

hi @Anonymous 

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
Anonymous
Not applicable

@az38 thanks for that - works for me 🙂

 

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

az38
Community Champion
Community Champion

@Anonymous 

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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

 

HI @Anonymous ,

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
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

Hi @Anonymous ,

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
If this post helps, please consider accept as solution to help other members find it more quickly.
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.