Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Dax Calculation - Cost Per Thousand and Number Formatting

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 ...
Need Help
Show and Tell
Tips and Tricks
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Anonymous,

     

    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. :smileyhappy:

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi Anonymous,

 

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. :smileyhappy:

 

Regards

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    dkay84_PowerBI
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

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