Percentage of total calculation

[Anonymous]

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

Any advice?

[az38]

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

[Anonymous]

@az38 thanks for that - works for me 🙂

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

[az38]

@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

[parry2k]

@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

[Anonymous]

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'

[Anonymous]

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

[Anonymous]

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

[Anonymous · 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

[parry2k]

@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