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.