Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Hi @Anonymous ,
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êsHi @Anonymous ,
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êsHi 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:
Hi @Anonymous ,
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êsHi , 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
Hi @Anonymous ,
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êsThank you for your help that ' great
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |