cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Creating new variables with DAX (Dataset example included)

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:

 

Screenshot1.JPG

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

2 REPLIES 2
Super User
Super User

Re: Creating new variables with DAX (Dataset example included)

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Anonymous
Not applicable

Re: Creating new variables with DAX (Dataset example included)

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

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,186)