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])
    )
)

     

     

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Anonymous 

If all columns are in the same table you may try

Avg CTR =
MAXX (
    SUMMARIZE (
        'GSC query analysis',
        'GSC query analysis'[Start of Month],
        'GSC query analysis'[Category],
        'GSC query analysis'[Query]
    ),
    CALCULATE ( DIVIDE ( [Sum Clicks], [Sum Impressions], 0 ) )
)
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Since the 'Start of Month' column is from the Calendar Lookup Table, so I changed that line in your code, so it looks below:

 

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

 

 This is how it looks like 

Still not correct..

And if choosing multiple queries, this is how it looks like:

Not correct either.

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Anonymous 

    Please try

    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 ) )
)
    • 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