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