Funnel chart not functioning as expected

[vickyd]

I am trying to display the total savings by stage where the stages are Identified, Solutioned, Executed, in that order. 

Total savings are 86M, so in the Funnel chart I was expecting to see 86M as Identified, then 16M as Solutioned and 9M as Executed, thus showing the progression through each stage. Isn't that how it's supposed to work? 

[v-shex-msft · Accepted solution]

HI @vickyd ,

As lbendlin said, you can't directly use raw table fields to achieve your requirement, you need coding some measure formals.

Steps:

1. Create a new calculated table for setting data levels of each status.

Aggregate Level = 
ADDCOLUMNS (
    VALUES ( Raw[Status] ),
    "Level", SWITCH ( [Status], "Identified", 1, "Solutioned", 2, "Executed", 3 )
)

2. Add a measure formula to summarize amounts based on its data level.

Measure = 
CALCULATE (
    SUM ( Raw[Amount] ),
    FILTER (
        ALLSELECTED ( Raw ),
        LOOKUPVALUE ( 'Aggregate Level'[Level], 'Aggregate Level'[Status], Raw[Status] )
            >= LOOKUPVALUE (
                'Aggregate Level'[Level],
                'Aggregate Level'[Status], MAX ( Raw[Status] )
            )
    )
)

Regards,

Xiaoxin Sheng

[amitchandak]

@vickyd ,Seem like it does not work the way you want

https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-funnel-charts

You can log an idea for that.

https://ideas.powerbi.com/ideas/

[lbendlin]

No, that's not how Funnel charts work. Try a waterfall but that's likely also not helping you.

You can create a measure that sums up your stages in the way you desire, and then you can feed the funnel chart with that measure.