Dynamic Grouping on Sum

[kohlivinayak]

I have data like

userid        |       profile percent        |         Date            |

1               |           20                     |      jan 10            |

1               |            30                   |    jan 20               |

2               |            55                   |      jan 7               |

2               |            30                   |       jan 25            |

we have date selection and on the selected date we want to show data like

0-50 percent    |       no of users  

51-75  percent  |       no of users  

75 - 100 percent  |      no of users

profile percentage will be added till the selected date

Thanks is advance

[Phil_Seamark]

Hi @kohlivinayak

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

[kohlivinayak]

Hi @Phil_Seamark

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

[Greg_Deckler · Accepted solution]

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]

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####****")

[Greg_Deckler]

Where does the date on your x-axis come from? Generally, the way you do this is to use a VAR to grab the MAX of the date on your x-axis. Then it's a simple FILTER. Also, you might want to check out my quick measures here that do some fancy stuff with dates and categorizing stuff in those dates.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[kohlivinayak]

Exact Scenario  - I really need this urgent. Thanks in Advance

Table I have

User 1 have avatar - legend from jan 1 till jan 14 and master from jan 15 onwards

User 2 have avatar - master from jan 1 till jan 14 and legend from jan 15 onwards

User 3 have avatar - tyro from jan 6 till jan 12 and master from jan 12 onwards

I want to show this on a line trend with avatar on being the legend showing count of users in different avatars for the timeline (all days from first date till selected date)

Lets say selected date is jan 20

legend will have one count from jan 1 till 20

master will have one count from jan 1 till jan 11 and two count from 12 till 20

tyro will have one count from jan 6 till jan 11

[kohlivinayak]

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