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 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!!!

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

     

     

15 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi tamerj1 

      I have 2 tables:

      1 - Calendar Lookup Table

       

      2 - GSC query analysis Table

      And they are connected by the Date Column

       

  • 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 ) )
)
  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      I tried this code, but they all show 1