Dynamically calculate measures based on a flag within levels of hierarchy

[saravananravim]

Hi,

I have a clustered bar chart with hierarchical data on Axis and couple of measures in Value. Sample source data looks like below:

Here the hierarchy is Object->Sub-Object->Leaf-Object.

The issue I have is that I want to display the Staging Count and Final Count values where the Main flag for Object='Y' for Parent i.e. Object. 

Similarly, I want to display count values where the Main flag for Sub-Object ='Y' for Sub-object when I drill down to the Sub-object level.

Current behaviour of PBI is its summing using SUM(Staging Count) for object and sub-object levels, by default.

What I have tried: Used analytical functions in Oracle to find the right count that has flag set to 'Y' and use FIRST() to display the count. But the problem with that is when I drill down the count value does not change which is expected from PBI. How do I dynamically change the flag fields at different levels of hierarchy and display its count.

Thanks

Rav

[v-lid-msft · Accepted solution]

Hi @saravananravim ,

We can create two measures using following DAX to meet your need.

StagingCountMeasure =
IF (
    ISINSCOPE ( 'Table'[Leaf-Object] ),
    CALCULATE ( SUM ( 'Table'[Staging Count] ) ),
    IF (
        ISINSCOPE ( 'Table'[Sub-Object] ),
        CALCULATE (
            SUM ( 'Table'[Staging Count] ),
            FILTER ( 'Table', 'Table'[Main flag for Sub-Object] = "Y" )
        ),
        CALCULATE (
            SUM ( 'Table'[Staging Count] ),
            FILTER ( 'Table', 'Table'[Main flag for Object] = "Y" )
        )
    )
)

FinalCountMeasure =
IF (
    ISINSCOPE ( 'Table'[Leaf-Object] ),
    CALCULATE ( SUM ( 'Table'[Final Count] ) ),
    IF (
        ISINSCOPE ( 'Table'[Sub-Object] ),
        CALCULATE (
            SUM ( 'Table'[Final Count] ),
            FILTER ( 'Table', 'Table'[Main flag for Sub-Object] = "Y" )
        ),
        CALCULATE (
            SUM ( 'Table'[Final Count] ),
            FILTER ( 'Table', 'Table'[Main flag for Object] = "Y" )
        )
    )
)

Then we can create the chart as you wish.

BTW, pbix as attached.

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more