Forum Discussion

kohlivinayak's avatar
kohlivinayak
Resolver I
8 years ago
Solved

Dynamic Grouping on Sum

I have data like   userid        |       profile percent        |         Date            | 1               |           20                     |      jan 10            | 1               |        ...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    Referred here from another thread, try this:

     

     

    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.

     

     

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

Hi kohlivinayak

 

Can your end user pick any random date, or only a date listed in your dataset?

kohlivinayak's avatar
kohlivinayak
Resolver I
8 years ago

Hi Phil_Seamark

 

User can pick any random date. for that i have a date dimension.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Referred here from another thread, try this:

     

     

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

    • kohlivinayak's avatar
      kohlivinayak
      Resolver I
      8 years ago

      Hi Greg_Deckler

       

      Need a help with formula

       

      I am ploting a line trend on x-axis I have date.
      From this formula I want to count rows which have Max_dt < the date on the x-axis.

      Please help me how to do that.

       

      Measure Try =
      var _maxdt = CALCULATE(MAX('Employee Skills'[UpdatedDate]),FILTER('Employee Skills','Employee Skills'[UpdatedDate]<=TODAY()))
      var _sumTable = SUMMARIZE('Employee Skills','Employee Skills'[UserId],'Employee Skills'[Avatar],"Max_Dt",_maxdt)

      return
      CALCULATE(COUNTROWS(_sumTable),"****####what_to_put_in_here####****")