cancel
Showing results for
Did you mean:
Highlighted
Helper V

Funnel chart not functioning as expected

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support

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 =
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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
3 REPLIES 3
Highlighted
Super User II

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.

Highlighted
Super User IV

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

Proud to be a Super User!

Highlighted
Community Support

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 =
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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Announcements

Power Platform Community Conference

Check out the on demand sessions that are available now!

Microsoft Power Platform Communities

Check out the Winners!

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors