Incorrect Measure Total

[Anonymous]

Hello All,

I am working on a project to calculating users' time value by choosing different transporation method. For example, I had a raw data like below.

It include Trip, Passenger, numbers of time they go on this trip, time of different transporation and time difference compare taxi to other transportation(Train - Taxi or Subway - Taxi). 

And then I will have a slicer to allow user to choose their production rate and use that to times the time difference and frq. (Time value = frq* Production Rate* diff vs. XX)

I am trying to do it in power bi and each row shows fine but "measure total" keep gives me a wrong num. The below table is the ideal result I am trying to get.

 

I hope to have two slicers on selecting production rate and passenger and only showing top 2 frquent trip this passenger took in the past. In the end, I hope to get the sum time value of how many time value this passenger can save by selecting Taxi.

I have been stuck in this problem for a week. Please let me know if anyone has an idea. Thanks!

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

Based on my test, we can take the following steps to meet your requirements.

1.Enter the data as you shared and create the measures to get the time values per way. Then we can get correct total and filter the table visual based on the rank.

Time value sub = SUM(Table1[Frq])*SELECTEDVALUE('Production Rate'[Production Rate])*SUM(Table1[Diff vs.subway])

Time value sub2 = SUMX(Table1,[Time value sub])

Time value train = SUM(Table1[Frq])*SELECTEDVALUE('Production Rate'[Production Rate])*SUM(Table1[Diff vs.Train])

Time value train2 = SUMX(Table1,[Time value train])

frqs = SUM(Table1[Frq])

rank = IF(ISBLANK([frqs]),BLANK(),RANKX(ALLSELECTED(Table1),[frqs],,DESC,Dense))

2.To get the total time that could be saved by selecting taxi. We can create the measure as below.

save time sub = SUM(Table1[Diff vs.subway])*SUM(Table1[Frq])

save time s = SUMX(Table1,[save time sub])

save time train = SUM(Table1[Diff vs.Train])*SUM(Table1[Frq])

save time t = SUMX(Table1,[save time train])

Here is the result for your reference.

For more details, please check the pbix as attached.

https://www.dropbox.com/s/r81zv1xngb8qgo9/Incorrect%20Measure%20Total.pbix?dl=0

Regards,

Frank

[Anonymous]

Thank you so much, Frank!