Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
@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.
@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
Proud to be a Super User!
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |