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

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
Icon for Community Champion rankCommunity 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
      Icon for Community Champion rankCommunity 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.