Fixed Measure

[JGG]

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?

[v-jingzhang · Accepted solution]

@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.

[DataInsights]

@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
    vResult

[JGG]

Thanks. 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.

[lbendlin]

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.