Forum Discussion

JGG's avatar
JGG
Helper I
5 years ago
Solved

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 vi...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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's avatar
DataInsights
Super User
5 years ago

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's avatar
    JGG
    Helper I
    5 years ago

    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.