Set Analysis in Gauge Chart

[Anonymous]

Hello!   I need help on this...This drive me  crazy for a couple of hours now...

I have a field "Category" that has 4 items :

New

Cancel

Forecast

Defer

And I created a gauge chart to compute only the 'NEW'/Total Value (excluding "CANCEL").... I placed a formula  =only(({<Category={'New '}>}[Total Value]))  in the dimension  ( to keep  the chart steady at NEW?)   and  expression

=sum({$<Category={'NEW'}>}[Total Value])/sum({$<Category -={'Cancel'}>}[Total Value])

My objective is to keep the data steady  at New/Total Value no matter  what I select in the Category Box...What happens, the figure changes when I select other category like  Defer, Cancel... I guess this has something to do with the "Total Value"  What expression should I use that it will still sum  all the Total Value (in the category box)  even if I select on 1 item?

Tks.

[Gysbert_Wassenaar]

If you want the expression to ignore selections use a 1 instead of $:

=sum({1<Category={'NEW'}>}[Total Value])/sum({1<Category -={'Cancel'}>}[Total Value])

[kaushiknsolanki]

Hi,

     Try this.

=sum({$<Category={'NEW'}>}[Total Value])/sum({$<Category -={'Cancel'}>}total [Total Value])

Rergards,

Kaushik Solanki

[jonathandienst · Accepted solution]

Hi

You dont need a dimension in the guage chart. Modify your expression to ignore selections:

=sum({1<Category={'NEW'}>}[Total Value])/sum({1<Category -={'Cancel'}>}[Total Value])

Hope that helps

Jonathan

[Anonymous]

Thanks Jonathan....but I still need to improve   my formula.. My total value is considering all quarters as the divisor...How do I add it in my formula to divide only the  the latest quarter (maybe using the min?) in statement below...

How can I add it in my statement using  other field Fiscal-Qtr like Q3 and Q4...to get only the Q3 of

sum({1<Category -={'Cancel'}>}[Total Value])