Forum Discussion

sandeepthachan's avatar
sandeepthachan
Regular Visitor
9 years ago
Solved

Funnel chart with multiple measure values

Hi All,      I have created a funnel chart with count of opportunity based on the stage values.  Now we need to add another measure value in parallel to the count of oppportunity. How can we achie...
Tips and Tricks
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi sandeepthachan,

     

    According to your description above, you may need to use two different Funnel charts to show the two measures individually, and turn off Category labels for one of the Funnel charts under Format tab, then move the two Funnel charts shown parallelly on the report in this scenario.:smileyhappy:

     

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi sandeepthachan,

 

According to your description above, you may need to use two different Funnel charts to show the two measures individually, and turn off Category labels for one of the Funnel charts under Format tab, then move the two Funnel charts shown parallelly on the report in this scenario.:smileyhappy:

 

 

Regards

sandeepthachan's avatar
sandeepthachan
Regular Visitor
9 years ago

Hi v-ljerr-msft,

 

 We are looking to get the both the measure values in one single funnel chart. Is there any way to change the display value of a chart to another value?

 

 

Regards,

Sandeep

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi sandeepthachan,

     

    I don't think it is possible to do it currently. We can only put the other measure on the Tooltips field of the chart, and the value of the measure will show up when we hover over an item on the chart.:smileyhappy:

     

    Regards

  • hymieho's avatar
    hymieho
    Resolver I
    9 years ago

    Here is how I did it:

    1. Create a measure for each value you want to show (ex: count of opps, opp value, opp volume). Here is an example:

    Open Opp Count = 
VAR OpenOpps = CALCULATE(COUNTA('Opportunities'[Unique ID]), Opportunities[Opp State]="Open")
RETURN
IF(ISBLANK(OpenOpps),0,OpenOpps)

    2. In Power Query, create a disconnected table then create a filter with the [Display In] column:

    #table(
 {"ID", "Display In", "Sort"}, 
 {
  {1, "Count of Opps", 1},
  {2, "Pipeline ($)", 2},
  {3, "Pipeline (lb)", 3}

 }
)

    3. Create create a "display" measure that returns the value of a measure depending on the filter in step 2.  

     

    Open Opps Display Value = 
VAR 
  Total=
IF(
HASONEVALUE('Display Units'[Display In]), 
SWITCH(VALUES('Display Units'[ID]),
1, [Open Opp Count],
3, [Open Opp LVOL],
2, [Open Opp LCUR]
)
,  [Open Opp Count])

RETURN
IF(ISBLANK(Total),0,Total)

    4. Now drag the measure [Open Opps Display Value] into a visualization. When you change the filter, the Dispay Value will change accordingly.