Creating a measure for Cost

[VulcanPromance]

I have this visualization

The cost license measure calculates price*count. However, I need an if statement to be able to calculate cost differently if the quantity type is pr license or total.

So the current Cost LIcense looks like this

Cost_Licenses = SUMX(Licenses;[Count]*([Price]))

And I thought I could just replace the above code with
Cost_all = IF(Licenses[Quantity_Type]="Total";SUMX(Licenses;1*([Price]));SUMX(Licenses;[Count]*([Price]))

However, it says it cannot determine the quantity type.

Count, and Cost Lisences are Measures

The fact table has a User ID, a License type, a bundle name, a price and a quantity type.

What am I doing wrong?

[sdjensen]

Hi @VulcanPromance,

It seem like you have all 3 columns availeble in the same table, so why not just add your cost calculation as a calculated column instead of a measure? I am aware of the extra cost involved with calculated columns, but it's an easy fix and you would need a lot of data for this to have a mentionable performance penalty in this case.

Cost_Licenses = 
IF(
    Licenses[Quantity_Type] = "Total";
    Licenses[Price];
    Licenses[Price] * Licenses[Count]    
)

Should also work as a measure like this:

Cost_Licenses_Meas = 
VAR CostTotal = CALCULATE( SUM(Licenses[Price] ); Licenses[Quantity_Type] = "Total" )
VAR CostPrLic = CALCULATE( SUMX( Licenses; Licenses[Price] * Licenses[Count] ); Licenses[Quantity_Type] <> "Total" )
RETURN
CostTotal + CostPrLic

[VulcanPromance]

@sdjensen 

Hi.

Using this formula works (I just swapped SUM to MAX under var CostTOtal). I have no idea why.

Cost Measure =

VAR CostTotal = CALCULATE(MAX(Licenses[Price]);Licenses[Quantity_Type]="Total")

VAR CostPrLic = CALCULATE(SUMX(Licenses; Licenses[Price]*Licenses[Count]);Licenses[Quantity_Type]<>"Total")

RETURN

CostTotal + CostPrLic

[VulcanPromance]

Hi @sdjensen 

All versions yield the same result.. maybe it wasnt too clear..

For quantity type Total the Cost_Licenses should be 482. Cause its an enterprise cost. I.e. Portal access costs 482Euro a month regardless of count.

But as you can see your Measure and your Column yields the same result as my Cost Licenses which does not contain an IF.

[sdjensen]

@VulcanPromance,

This is very strange, because I created a table as manual input into Power BI and both worked in my case.

You are 100% sure that is just saying "Total" without exceeding or preceeding spaces etc.?

[sdjensen]

@VulcanPromance,

Here is my result with calculation as column and measure

[VulcanPromance]

@sdjensen 

Im very new to Power BI, but the column one kinda makes sense.. I created the column on the table. And for each license it puts on the price obviously.. So Price = CostColumn for each license in the table.

Cost_Licenses = 
IF(
    Licenses[Quantity_Type] = "Total";
    Licenses[Price];
    Licenses[Price] * Licenses[Count]    
)

I reckon it runs this code pr line/record and since a record = 1 count the price = cost

So for each record that has a quantity type of Total it lists 482. When visualizing this column, it multiplies the numbers

EDIT: Yes, I checked that Total does not have any space before or after.

I'm suspecting this has something to do with how I set up the tables? I merged in prices, bundlename and quantity type from another table in power query.

[Anonymous]

Hey! First things first I'd advise you to use calculate(sum(.....)) instead of sumx(....) honestly it's easier to write and it performs better especially if you have larg(er) datasets. This should be more readeable:

sum(Calculate(1 * Price, Quantity_Type = "Total"), Calculate(Price * quantity, Quantity_Type <> "Total"))

or Calculate(1 * Price, Quantity_Type = "Total") + (Calculate(Price * quantity, Quantity_Type <> "Total")

Something like this should work I suppose.

[VulcanPromance]

Cost_Licenses = CALCULATE(SUM([Count]*[Price])) does not work.

Couldnt get it to work on your other two either

Calculate(1 * Price, Quantity_Type = "Total") + (Calculate(Price * quantity, Quantity_Type <> "Total")

Had to re-write them some, cause I assume I need the [] around the columns.. and I have no Quantity table. its in the license table

So tried this.

sum(Calculate(1 * [Price]; [Quantity_Type] = "Total"); Calculate([Price] * Licenses; [Quantity_Type] <> "Total"))

It reports to many arguments in SUM

Tried this

Calculate(1 * [Price]; [Quantity_Type] = "Total") + Calculate([Price] * Licenses; [Quantity_Type] <> "Total")

IT reports value of quantity type cannot be determined.