Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everyone,
I need help with DAX measure to calculate total cost per product/tariff. Namely, in the table "Customers" i have different tariffs with coresponding users in number column. I need to take the cost per tariff from the table "Content Costs" column "Cost" and multiply with the number of customers. The result should be total costs per tariff.
I tried with the sumx,
Solved! Go to Solution.
Hi @Anonymous ,
Is this the result?
//in 'Customer' table
Column =
LOOKUPVALUE(
'Content Cost'[Cost],
'Content Cost'[Customer Layer], Customers[Customer Layer],
'Content Cost'[Package], Customers[Package],
'Content Cost'[Product Layer], Customers[Product Layer],
'Content Cost'[Technology], Customers[Technology]
)
Column 2 = [Column] * [number per Package per product]
Regards,
Lionel
Hi @Anonymous ,
Is this what you want to calculate?
Cost per package per product =
CALCULATE(
SUM('Content Cost'[Cost]),
ALLEXCEPT(
'Content Cost',
'Content Cost'[Package], 'Content Cost'[Product Layer]
)
)
number per Package per product =
CALCULATE(
SUM(Customers[Number]),
ALLEXCEPT(
Customers,
Customers[Package],Customers[Product Layer]
)
)
total cost per product = [numbers per Package per product] * [Cost per package per product]
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thanks for your support. Unfortunattely, the proposed solution gives wrong results. For example, the correct results for the selection Residential ->TV -> Fix ->optic -> Optic has 86 customers and multiplied by the costs should result in 21.500, not 417.960 as in the screenshot below.
thanks,
Goran
Hi @Anonymous ,
Is this the result?
//in 'Customer' table
Column =
LOOKUPVALUE(
'Content Cost'[Cost],
'Content Cost'[Customer Layer], Customers[Customer Layer],
'Content Cost'[Package], Customers[Package],
'Content Cost'[Product Layer], Customers[Product Layer],
'Content Cost'[Technology], Customers[Technology]
)
Column 2 = [Column] * [number per Package per product]
Regards,
Lionel
Thank you, it worked!
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |