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

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]

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

5 Replies

  • 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