Gauge Chart Dynamic Maximum Value

[Anonymous]

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 to use the gauge chart to show the maxium [Avg CTR] (click-through-rate) that a query (a search term) has ever gotten in history.

Below in this picture, the Avg CTR in the matrix is a measure that I defined:

Sum Clicks = 
IF(
    ISBLANK(
        SUM('GSC query analysis'[Clicks])
    ),
    0,
    SUM('GSC query analysis'[Clicks])
)

Sum Impressions = 
IF(
    ISBLANK(
        SUM('GSC query analysis'[Impressions])
    ),
    0,
    SUM('GSC query analysis'[Impressions])
)

Avg CTR =
IFERROR(
    ('Measure Table'[Sum Clicks]/'Measure Table'[Sum Impressions]),
     0
)

I want the Max value of the Gauge Chart to show "0.25" in this case.

---

In another case, I can also select multiple queries, it will then calculate the Avg CTR of a month from those selected queries. And then I would want the Max Value of the Gauge Chart to show "0.20".

I am guessing I will have to use ALLSELECTED to clear the filter in the gauge chart visual but keep the slicer filters, and then get the Avg CTR, then get the Max of the Avg CTR, but I don't know how to do it.

Your help is much appreciated. Many thanks!!!

[tamerj1]

@Anonymous 

Also please try

Max CTR =
CALCULATE (
    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 ) )
    ),
    REMOVEFILTERS ( 'GSC query analysis' ),
    ALLSELECTED ( 'Calendar Lookup' )
)

[Anonymous]

I tried this code, but they all show 1

[tamerj1]

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

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]

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

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]

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

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]

@Anonymous 

Ok. Can we try more simple

Max CTR =
MAXX ( ALLSELECTED ( 'Calendar Lookup'[Start of Month] ), [Avg CTR] )

[Anonymous]

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

[tamerj1]

@Anonymous 

For some reason, no matter what we do ot only shows the value of the last date! 

[Anonymous]

Now you mentioned that it always show the last date, I realized it is because I set the visual filter to be "Start of Month" - "Top 1" - "Latest Start of Month" so that I can show the current CTR (0.07), but then it will cause the Max CTR to not work. 

If I clear that fitler, the Max CTR will be correct! but then the current CTR will be incorrect.

That being said, we can tackle it from a different direction, trying to create a new measure to get the current CTR for the gauge chart. Do you know how I can write the code to get the current CTR for the gauge chart?

[Anonymous · Accepted solution]

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]

Hi @Anonymous 

is everything in one table?

[Anonymous]

Hi @tamerj1 

I have 2 tables:

1 - Calendar Lookup Table

2 - GSC query analysis Table

And they are connected by the Date Column