cancel
Showing results for
Did you mean:  Helper I

## How to create gauge charts KPIs that are not affected by filtering?

Hello,

I am trying to create a campaign gauge chart KPIs by channel type and filtering by account.

For instance I would like to compare total average CTR and total average Conversion rate against  specifc campaign CTR and Conversion rate by channel.

For instance if the campaign CTR or the campaign Conversion rate is below to its channel average then is "Red", elseif the campaign the campaign CTR or the camapiagn Conversion rate is above average of its channel then "Green" else "Yellow".

For example: this is the total CTR for paid search that I have created.

CALCULATE([CTR],FILTER('All Data','All Data'[Channel]="Paid Search"))

And this is the calculation for the KPIs:

SWITCH(
TRUE(),
[CTR]>[Total CTR Paid search],"#34eb52",
[CTR]=[Total CTR Paid search],"#eba534","#eb4934")

I immagine that I  have to create these formulas for each channel types.

My issue is happening  when trying to compare the total CTR average for paid search to the campaign average for this channel.  However, when doing so, the total CTR average for paid search is being filtered by campaign type. But I do not want this to be filtered by campaign just by account and channel type

Please someone can help me in this?

1 ACCEPTED SOLUTION  Super User III

Hi @Marck ,

Beleive I have understood what you mean so you need to do the following measures:

``````Paid CRT =
VAR temp_table =
SUMMARIZE (
FILTER (
ALL ( Sheet1 ),
Sheet1[Channel] = "Paid Search"
&& Sheet1[Account] = SELECTEDVALUE ( Sheet1[Account] )
),
Sheet1[Account],
Sheet1[Channel],
"ClicksValues", SUM ( Sheet1[Clicks] ),
"ImpressionsValues", SUM ( Sheet1[Impressions] )
)
RETURN
DIVIDE (
SUMX ( temp_table, [ClicksValues] ),
SUMX ( temp_table, [ImpressionsValues] )
)

CRT = CALCULATE(DIVIDE(SUM(Sheet1[Clicks]), SUM(Sheet1[Impressions])))

CONd = SWITCH(
TRUE(),
[CRT]>[Paid CRT],"#34eb52",
[CRT]=[Paid CRT],"#eba534",
"#eb4934")``````

Result in attach PBIX file.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português 6 REPLIES 6  Super User III

Hi @Marck ,

Looking at the formula you are giving itr appears like you are making a calculated column.

You need to create a calculated measure and in this case since you want the averege you need to do something similar to:

``Total CTR Paid search = CALCULATE(AVERAGE(Table[CTR]),FILTER('All Data','All Data'[Channel]="Paid Search"))``

Then you need to make the average of the CRT to compare:

``````CONd = SWITCH(
TRUE(),
Average(Sheet1[CTR])>[Total CTR Paid search],"#34eb52",
Average(Sheet1[CTR])=[Total CTR Paid search],"#eba534",
"#eb4934")``````

Since this are measures the calculatioins will be made accordingly to the context so if you only add the campaign and type it will calculate accordinglty.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português   Helper I

Hi Thank you for your help ,

I just want to display the following measure as a total for the account by channel type when comparing with a  campaign CTR for the same channel and account:

CALCULATE(IFERROR((SUM('All Data'[Clicks])/SUM('All Data'[Impressions])),BLANK()),FILTER('All Data','All Data'[Channel Table]="Paid Search"))

For instance  I have an overall campaign  CTR for an account of  9% and for a specific campaign that i would like to compare is 6%. Hence ,when using the KPIs condition formula and  trying to compare the total account CTR against the campaign CTR no change is shown as the overall account CTR has been affected by the campaign filtering. And I do not want this to happen. How can i solve this?  Super User III

Hi @Marck ,

Sorry for the question but I'm not getting the results you are returning, the measure you have gave me it the same value has I have for the TOTAL CRT Paid search.

Were I get lost is when you refer to the 9% and the 6% I don't get does values when compared with the Paid Search values and the CRT you are calculating.

Can you explain a ittle bit better showing what are the numbers you are picking up.

Sorry once again but not really sure if I'm getting the full extent of your calculations.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português   Helper I

Hi ,  sorry it was my fault I  was referring to a different data.

I have amended the data source so you may understand me better.

My aim is to compare specific  campaign average by account and channel type  against to the account overall averages by channel.

For instance, my Total Click through rate (CTR) formula: is CALCULATE(IFERROR((SUM('All Data'[Clicks])/SUM('All Data'[Impressions])),BLANK()),FILTER('All Data','All Data'[Channel Table]="Paid Search")) and this will be filtered by account via an external filter.

For example,  "Red cars" has a total average CTR for Paid search of 0.06%. And in the same account a campaign called "Red & White" has a paid search CTR of 0.05%

Then, I would like to use my KPIs colour formula into a gauge chart. Then , when filtering the gauge chart by campaign "Red & White" and account "Red Cars"  I am expecting to see  a red colour in the gauge chart as the  CTR is below the the total CTR Paid search average that we see for this account( 0.06%). Instead, the gauge chart is showing no difference because the Total Paid search CTR for the account has been filtered to 0.057%. But I do not want this to happen.

How can i solve this?

Hopfully, I have made myself clear

Thank you  Super User III

Hi @Marck ,

Beleive I have understood what you mean so you need to do the following measures:

``````Paid CRT =
VAR temp_table =
SUMMARIZE (
FILTER (
ALL ( Sheet1 ),
Sheet1[Channel] = "Paid Search"
&& Sheet1[Account] = SELECTEDVALUE ( Sheet1[Account] )
),
Sheet1[Account],
Sheet1[Channel],
"ClicksValues", SUM ( Sheet1[Clicks] ),
"ImpressionsValues", SUM ( Sheet1[Impressions] )
)
RETURN
DIVIDE (
SUMX ( temp_table, [ClicksValues] ),
SUMX ( temp_table, [ImpressionsValues] )
)

CRT = CALCULATE(DIVIDE(SUM(Sheet1[Clicks]), SUM(Sheet1[Impressions])))

CONd = SWITCH(
TRUE(),
[CRT]>[Paid CRT],"#34eb52",
[CRT]=[Paid CRT],"#eba534",
"#eb4934")``````

Result in attach PBIX file.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português   Helper I

Thank you for your help that ' great    