SUM of specific column based on measure selected, with respect to time slicer

[Anonymous]

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!

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

First select "Route Cost" columns and unpivot columns.

Create a measure

Measure = SUM('Table'[Value])

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.

[amitchandak]

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])) 

[Anonymous]

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?