Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kohlivinayak
Resolver I
Resolver I

Dynamic Grouping on Sum

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

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

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Phil_Seamark
Employee
Employee

Hi @kohlivinayak

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hi @Phil_Seamark

 

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

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.

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

 

Table I have

 

Screen Shot 2018-07-09 at 4.07.39 PM.png

 

 

 

 

 

 

 

 

 

 

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

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.