Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Gauge Chart Dynamic Maximum Value

Hi all, I am trying to create a gauge chart with a dynamic maximum value based on the slicer filters (Category & Query & Date Range) on the dashboard. I am in the marketing industry and am trying t...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi tamerj1 
    I got it to work!! Thank you so so much for helping me out! 

    Current CTR (Gauge) = 
CALCULATE(
    [Avg CTR],
    FILTER(
        'GSC query analysis',
        RELATED('Calendar Lookup'[Start of Month]) = MAXX('Calendar Lookup', 'Calendar Lookup'[Start of Month])
    )
)

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hey tamerj1 

I used your code, but it's giving me the same result, so it's still incorrect.

Single query selected:

 

Multiple queries selected:

But I think we are getting there. I think it's still doing the calculatation on the row basis, instead of column as a whole.

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Anonymous 

It allways goves the vslue of the last date. I wonder why!

Would you please activate the row totals and see emwhat is the value shown at the total 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I used this code and turned on the subtotal in the matrix.:

     

    Max CTR = 
MAXX (
    CROSSJOIN (
        VALUES ( 'Calendar Lookup'[Start of Month] ),
        SUMMARIZE (
            'GSC query analysis',
            'GSC query analysis'[Category],
            'GSC query analysis'[Query]
        )
    ),
    CALCULATE ( DIVIDE ( [Sum Clicks], [Sum Impressions], 0 ) )
)

     

    It seems like if only selecting one query, the total of the Max CTR would show the correct value that I want!
    ex1: 

    ex2: 

    However, if selecting multiple queries. The total of Max CTR will show only the biggest max ctr out of all the individual queries (0.25), instead of collectively "Sum Clicks of all queries / Sum Impressions of all queries" which shuold be 0.07 in this case.

    • tamerj1's avatar
      tamerj1
      Community Champion
      4 years ago

      Anonymous 

      Ok. Can we try more simple

      Max CTR =
MAXX ( ALLSELECTED ( 'Calendar Lookup'[Start of Month] ), [Avg CTR] )
      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        I actually have tried this line of code before. It showed correctly in the matrix, but incorrect in the gauge chart.