Forum Discussion

Emmaxson's avatar
Emmaxson
Advocate I
9 years ago
Solved

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 x...
Need Help
Tips and Tricks
Tutorial Requests
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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's avatar
Emmaxson
Advocate I
9 years ago

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's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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's avatar
      Emmaxson
      Advocate I
      9 years ago

      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's avatar
        v-huizhn-msft
        Microsoft Employee
        9 years ago

        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