Group by Measures | Pivot on Calculated Values

[Emmaxson]

Hi Community,

so I have data of persons who have achieved trips over time like this:

Date | Name | Trip 

xx1   | Max     | 1

xx1   | Jerry    | 1

xx2   | Max     | 1

xx2   | Dupe   | 1

xx3   | Max     | 1

xx4   | Dupe   | 1

and I have a measure Total Trips = sum( [Trip] )  which sums up the Trips column

I want to be able to group by the Total Trips achieved at every point in time (filtered on date).

I mean something like this:

For Filtered Date : xx1 - xx3 

Name  | 1 Trip | 2 Trips | 3 Trips | >3 Trips | Total

Max     |     0     |      0      |     3       |      0        |    3

Jerry    |     1     |      0      |     0       |      0        |    1

Dupe   |     1     |      0      |     0       |      0        |    1

Total   |    2     |      0      |     3      |      0        |    5

where

* 1 Trip  := Total Trips = 1

* 2 Trips := Total Trips = 2

* 3 Trips := Total Trips = 3

* >3 Trips := Total Trips > 3

Please help me.

Thanks.

[E]

[v-huizhn-msft]

Hi @Emmaxson,

Please create four measures using the formulas below.

1 Trip = IF(Test[Total Trips]=1,1,0)
2 Trip = IF(Test[Total Trips]=2,2,0)
3 Trip = IF(Test[Total Trips]=3,3,0)
>3 Trip = IF(Test[Total Trips]>3,Test[Total Trips],0)

Then create a slicer including the date, create a matrix visual, select the [name] as row, the measures as values fields. You will get the expected result.



Please download the attachment to test and review.

Best Regards,
Angelia

[Emmaxson]

Thanks for this @v-huizhn-msft BUT the total row is not working fine. Please check. For each measure, it gives a total of 0 aside from the last measure; how can we work around this? Because in the end, I want to know how many people fall under each category.

Also, have a look at this: http://www.daxpatterns.com/dynamic-segmentation/ 

I tried working with Dynamic Segmentation but was not successful in my exploits.

Please help.

Thanks

[v-huizhn-msft · Accepted solution]

Hi @Emmaxson,

Please use the formulas below

1 Trip = IF(HASONEFILTER(Test[Name]),IF(Test[Total Trips]=1,Test[Total Trips],0),SUMX(FILTER(VALUES(Test[Name]),Test[Total Trips]=1),IF(Test[Total Trips]=1,Test[Total Trips])))+0

2 Trip = IF(HASONEFILTER(Test[Name]),IF(Test[Total Trips]=2,Test[Total Trips],0),SUMX(FILTER(VALUES(Test[Name]),Test[Total Trips]=2),IF(Test[Total Trips]=2,Test[Total Trips])))+0

3 Trip = IF(HASONEFILTER(Test[Name]),IF(Test[Total Trips]=3,Test[Total Trips],0),SUMX(FILTER(VALUES(Test[Name]),Test[Total Trips]=3),IF(Test[Total Trips]=3,Test[Total Trips])))+0

>3 Trip = IF(HASONEFILTER(Test[Name]),IF(Test[Total Trips]>3,Test[Total Trips],0),SUMX(FILTER(VALUES(Test[Name]),Test[Total Trips]>3),IF(Test[Total Trips]>3,Test[Total Trips])))+0

You will get right result.

ANGELIA

[Emmaxson]

@v-huizhn-msft This is good. Does this job. #ThumbsUp

However can you help me understand it so I can reuse it where and when necessary in other ways.

For instance, I want to check for each 1 Trip, 2 Trips, etc.; I want to know the count of drivers that fall into each category at every point in time.

Looking forward to your favourable response.

Thanks once again.

[v-huizhn-msft]

Hi @Emmaxson,

You can review this article, which discribes the reason in details. And how to get the expected total, it depands on your measure to calculate the result. Sometimes, you just need to add SUMX around the formula like this thread.

Best Regards,
Aneglia