Forum Discussion

RichardP's avatar
RichardP
Helper I
8 years ago
Solved

Grouping values from measures

Hi,   I have a table that looks something like this:   AccountID UserID Visits Help requests abc 111 5 0 abc 456 0 1 abc 789 15 2 def 555 2 2 def 666 0 1  ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    You should be able to do it like this:

     

    Table = SUMMARIZE('TABLE','TABLE'[Column],"MyMeasure",[Measure])
Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

OK, based on the data in the other post, I created a Category table with:

 

Category

0-50 percent
51-75 percent
75-100 percent

 

And a measure like this:

 

Measure = 
VAR __Date = MAX('Table'[date])
VAR __Category = MAX('Categories'[Category])
VAR __Low = 
SWITCH(
    __Category,
    "0-50 percent",0,
    "51-75 percent",.51,
    "75-100 percent",.75
)
VAR __High = 
SWITCH(
    __Category,
    "0-50 percent",.5,
    "51-75 percent",.74,
    "75-100 percent",1
)
VAR __tmpTable = SUMMARIZE('Table','Table'[userid],"__Percent",MAX('Table'[profilepercent]))
RETURN COUNTROWS(FILTER(__tmpTable,[__Percent]>=__Low && [__Percent]<=__High))

PBIX is attached.

 

 

kohlivinayak's avatar
kohlivinayak
Resolver I
8 years ago

Hi Greg_Deckler

You rock man!!!

 

It worked after small tweak. And you have given me a whole new dimension to figure new stuff i can do with this.

 

The actual query which worked for me


User Count Profile Percentage =
VAR __Category = MAX('Category'[Category])
VAR __Low =
SWITCH(
    __Category,
    "0-50 percent",0,
    "51-75 percent",51,
    "75-100 percent",75
)
VAR __High =
SWITCH(
    __Category,
    "0-50 percent",50,
    "51-75 percent",74,
    "75-100 percent",100
)
//VAR __tmpTable = SUMMARIZE('Table','Table'[userid],"__Percent",SUM('Table'[profilepercent]))
//RETURN COUNTROWS(FILTER(__tmpTable,[__Percent]>=__Low && [__Percent]<=__High))

var _Temp_tbl = SUMMARIZE('Periodic %age completion update','Periodic %age completion update'[UserId],"_sumPer", CALCULATE(SUM('Periodic %age completion update'[ProfilePercentage]),FILTER('Periodic %age completion update','Periodic %age completion update'[UpdatedDate]<='Date Dim'[Date selected] && 'Periodic %age completion update'[UpdatedDate]>= [Least Date])))

return
COUNTROWS(FILTER(_Temp_tbl,[_sumPer] >= __Low && [_sumPer] <= __High))

 

Thanks !!!!