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