Forum Discussion
Helper IICreating a measure for Cost
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
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?
8 Replies
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.
VulcanPromanceCost_Licenses = CALCULATE(SUM([Count]*[Price])) does not work.Couldnt get it to work on your other two eitherCalculate(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 tableSo tried this.sum(Calculate(1 * [Price]; [Quantity_Type] = "Total"); Calculate([Price] * Licenses; [Quantity_Type] <> "Total"))It reports to many arguments in SUMTried thisCalculate(1 * [Price]; [Quantity_Type] = "Total") + Calculate([Price] * Licenses; [Quantity_Type] <> "Total")IT reports value of quantity type cannot be determined.
- sdjensen
Solution Sage
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
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
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.?
- VulcanPromance
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")RETURNCostTotal + CostPrLic
