Dax Calculation - Cost Per Thousand and Number Formatting

[irnm8dn]

Trying to perform a calculation and having some challenges...which I think how Power BI is recognizing my data.

The calculation should be:

Budget, Divided by Cost Per Thousand * 1,000

My limitations are:

• After import, I believe that Power BI is not recognizing Cost Per Thousand notr Budget in a proper format (see pic below) 
• 
  • The reason I think this, is that when I attempt the DAX statement using the New Measure Button in the ribbon, it does not give me the option to use these fields in my calculation.  i.e. Measure = DIVIDE( budget
• Secondly, I have an additional column called Contract Rate.  One of the values is "<10,000" and I think I need to find an alternative as the special character is limiting my ability to identify this column as a "Whole Number".

All suggestions are welcome.

Thank you.

[dkay84_PowerBI]

Your measure needs to be a calc column

[MFelix]

Hi @irnm8dn,

Yoir budget column is in the correct format that you can see because the epsilon sign. A measure is evaluated in the context of the cell evaluated in a dax formula, this.meaning that it take into.account if you are grouping the information for instance, its not only ar a row level. This means that to make your formula work you need to add some summarize formula try this.

Measur = DIVIDE (SUM(Table[Budget]), SUM(Table[Value]))

Don't know what is the value you are dividing by but when using a measure.you need to give.context to your.colums since the calculations csn be done at a row level or.group.level.

Regards,
MFelix

P.S. if any of the DAX experts see any error in my theorical explanation please correct me 🙂

[irnm8dn]

Thanks @MFelix

I am thinking we are almost there, but that the DAX formula may not be entirely correct.

Let me write this as a word problem so we can look at all the inputs and we can discuss a proper DAX calculation.

Johnny has a $500 budget to purchase apples.  The cost for apples is $20 cost per thousand.  How many apples can Johnny buy based on his budget.  $500/$20*1000 = 25,000 apples.

Thanks for the help.

[MFelix]

Hi @irnm8dn,

If that is the case then just multiply your cost per thousan by 1000 this should give you:

Measure = DIVIDE (SUM(Table[Budget]), SUM(Table[CostPerThousand])*1000)

Regards,

MFelix

[irnm8dn]

I don't think it should be summing the CPM.  Despite getting a calculation - it's not working properly.

Any thoughts?

[MFelix]

How do you ussualy do it with average value, max min?

[irnm8dn]

Hello @MFelix.

Simply put I take the Budget (Customer Spend), divided by the CPM "constant" * 1,000....essentially it give me the total number of units a customer could purchase based on budget.

The CPM is a "constant".

If you're interested, and looking for a full picutre if what I am trying to do, this may help.  I have added some sample files, because I am tied in knots over this one.  Thanks your help. http://community.powerbi.com/t5/Desktop/Slicer-Behavior-on-Tiles/m-p/188227#M82778

[v-ljerr-msft · Accepted solution]

Hi @irnm8dn,

1. Use the formula below to add a calculate column in your table.

Column = DIVIDE('Master Rate Data'[Budget],'Master Rate Data'[Cost-Per-Thousand])*1000

2. Then you should be able to use the formula below to create new measure, then show it on the report.

messure = 
IF (
    HASONEFILTER ( 'Master Rate Data'[Product] )
        && HASONEFILTER ( 'Master Rate Data'[Budget] )
        && HASONEFILTER ( 'Master Rate Data'[Spend Level] ),
    MAX ( 'Master Rate Data'[Column] )
)

Here is the modified pbix file for your reference. 

Regards

[irnm8dn]

@v-ljerr-msft

AMAZING!  Thank you so much.

One question, is there a way to make spend level formatted as a currency?  I belive the special characte of "<" is keeping me from doing this?

[dkay84_PowerBI]

No.  The special character means the whole value gets treated as a string, not a numeric value.