Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Emmaxson
Advocate I
Advocate I

Group by Measures | Pivot on Calculated Values

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]

1 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

View solution in original post

5 REPLIES 5
v-huizhn-msft
Employee
Employee

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.

1.PNG

Please download the attachment to test and review.

Best Regards,
Angelia

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

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

@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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.