Creating new variables with DAX (Dataset example included)

[Anonymous]

Hi all,

I hope everyone is doing well. My name is Ashley and I recently started to use PowerBI.

I wanted to do create new variables as part of my analysis. The example dataset is as below:

Based on the dataset, how do I use DAX to create new variables?

I wanted to create a new measure/new column which divides by the sum of clicks by the sum of impressions (The last two columns) based on different ad sizes, companies, campaigns, countries, device types or even media type. 

For e.g. I want to look at the clicks/impressions for China for instance or look at clicks/impressions for PC. 

Since there are a large number of countries, companies, campaigns, device types and media types, I don't think I can create a filter for each of the variable. In addition, the countries and campaigns are ever changing everytime I extract the data, this means I cannot create a specific filter for each campaign or country (e.g. Like a filter for China, filter for campaign A, filter for campaign B etc).

I am looking for a couple of DAX formula/function that allows me to aggregate the sum of clicks/sum of impressions for each country, company, campaign, device types and media types WITHOUT creating individual filters or a specific country or campaigns. 

If that sounds confusing, feel free to ask me any questions. I can always email you back or set up a Skype call if needed. If you are not sure about the answer, you can also direct me to someone that might know it!

Any help is appreciated! Thank you 🙂

Ashley

[Greg_Deckler]

You could create 3 measure:

SumClicks = SUM('Table'[Clicks])

SumImpressions = SUM('Table'[Impressions])

Measure = DIVIDE([SumClicks],[SumImpressions],0)

Since these are measures, you can put them into Table visualizations, etc and they will calculate based upon the filters in those visuals.

So, for example, if you put Country into a table visual, you will get a list of countries and these measures will return values for each country.

[Anonymous]

Hi Gregg, 

Thank you for your help!

Based on the 3 measures, I wanted to take a step further and create an average of the measure.

This is the formula that I came up with:

CTRMeasure = AVERAGEX('Clean data',DIVIDE([SumClicks]/[SumImpressions],0))

For some reason, no date is showing up when I use that formula and there are no error messages? Is it possible to provide me with some advice on this?

Thanks again 🙂

Ashley