Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi everyone,
I have a table similar to the sample below
Date | Route no. | Customer | Route Cost A | Route Cost B | Route Cost C |
01-Oct | 1 | A | 18,000 | ||
01-Oct | 1 | A | |||
01-Oct | 1 | A | |||
01-Oct | 1 | A | |||
01-Oct | 1 | A | |||
03-Oct | 2 | B | 21,000 | 444.01 | 166.50 |
03-Oct | 2 | A | |||
03-Oct | 2 | A | |||
03-Oct | 2 | C | |||
03-Oct | 2 | C | |||
03-Oct | 2 | A | |||
03-Oct | 2 | B | |||
04-Oct | 3 | A | 15,725.27 | 620.73 | |
04-Oct | 3 | A | |||
04-Oct | 3 | A | |||
04-Oct | 3 | C | |||
04-Oct | 3 | C |
What I'm trying to do is return the total cost of each customer, based on 1) the selected customer, and 2) the time slicer.
For example, if I choose customer A and set slicer to 01/10/2020 - 03/10/2020, I would get 18000 + 21000 = 39000 as result.
Your help would be much appreciated!
Solved! Go to Solution.
Hi @Anonymous
First select "Route Cost" columns and unpivot columns.
Create a measure
value 2100 belongs to customer b.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
First select "Route Cost" columns and unpivot columns.
Create a measure
value 2100 belongs to customer b.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
For the date, you should create a date dimension and join with your date and use the filter from date dimension
Customer if you create a dimension(Separate Table) it would better. Or you can use the filter from the same table
Measure like this should work
Measure =calculate(sum(Table[Route Cost A])+ sum(Table[Route Cost B])+ sum(Table[Route Cost C]))
Hi @amitchandak , thank you for your fast response.
I already have separate "Calendar" and "Customer" table in my model and tried your measure. But the problem is that when Customer A is selected, all rows of customer different than A will be removed. Therefore some of the cost for A will not be counted, and some cost not for A will be counted.
Can you please help me with another solution?
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
90 | |
80 | |
60 | |
59 | |
58 |
User | Count |
---|---|
155 | |
119 | |
104 | |
78 | |
71 |