Forum Discussion
Fixed Measure
Hi, I have some overhead cost on monthly basis that should be equaly distributed per customer based on the number of customers for each period.
My measure looks good, but it fails used in a table view for each customer shown in the figure. I do think there must be something wrong with my use of DISTINCCOUNT. It is not fixed and most likely gives just 1 for each customer in the table view.
So how can I force the use of total number of customers for each period?
JGG , change your measure like below then you will get the result you want.
CostAllPerCustomer = CALCULATE(SUM(CostTable[Cost]),CostTable[Type] IN {"All"})/CALCULATE(DISTINCTCOUNT(CustomerTable[CustomerId]),ALLEXCEPT(CustomerTable,CustomerTable[Period]))Best Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
4 Replies
JGG,
Try this measure. The Customer table appears to have one row per period and customer, so I believe a row count by period will work.
Cost All Per Customer = VAR vPeriod = MAX ( Customer[Period] ) VAR vCostRow = FILTER ( ALL ( Cost ), Cost[Period] = vPeriod && Cost[Distribution] = "All" ) VAR vNumerator = MAXX ( vCostRow, Cost[Cost] ) VAR vCustomers = FILTER ( ALL ( Customer ), Customer[Period] = vPeriod ) VAR vDenominator = COUNTROWS ( vCustomers ) VAR vResult = DIVIDE ( vNumerator, vDenominator ) RETURN vResultThanks. This seems to be a correct approach but I do have several rows per customer per periode. So I guess there would be a need to adjust according to that. The calculation however is extremely time consuming.
JGG , change your measure like below then you will get the result you want.
CostAllPerCustomer = CALCULATE(SUM(CostTable[Cost]),CostTable[Type] IN {"All"})/CALCULATE(DISTINCTCOUNT(CustomerTable[CustomerId]),ALLEXCEPT(CustomerTable,CustomerTable[Period]))Best Regards,
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
For both CALCULATE and DISCTINCTCOUNT you'll want to indiate the context. Currently you only remove the Type filter, you likely want to expand that context a bit more. You can do that by adding ALLSELECTED, for example.
