topic Re: Flag to mark two lowest values from Table. Grouped by other columns in DAX Commands and Tips

Flag to mark two lowest values from Table. Grouped by other columns

Pawel_1990 — Mon, 21 Oct 2024 11:57:42 GMT

Hello,

I am using table in direct query mode as source.
Table shows active users already grouped per Month, country, User_Tier, User_Group for last 12 weeks. Example below.

Example table is smaller, but by default each month based on Month_Key has 12 Reported_weeks. Combinations of possible User_Tier, User_Group differs - so it may be 1 row per Reported_week or 5 tows per reported_week.

Country	Month_Key	Reported_Week	USER_TIER	User_Group	Weekly_Users_Count
PT	20240901	202428	A	H	20
PT	20240901	202428	B	J	10
PT	20240901	202429	A	H	30
PT	20240901	202429	C	H	20
PT	20240901	202430	B	J	10
PT	20240901	202430	C	H	30
FR	20240901	202428	B	G	50
FR	20240901	202428	B	T	40
FR	20240901	202429	A	G	100
FR	20240901	202429	A	G	55
FR	20240901	202430	A	G	66

Currently there is a measure that caluclates a Weekly number of active users by:
# Weekly Active Users = Calculate(Divide(Sum(Table[Weekly_User_Count]),12))

Is there a possibility at DAX level to change the calculation to take only 10 weeks with best values for each month and country?
So basicly Flag two worst Reported_Week (sum of all combination of User_Tier, User Group).

I tried do it two ways and failed:
Idea #1 - FAILED
- Create Dax measure to detect lowest values. I used "MIN"

Idea #2 - FAILED
- Create a calculated Column to mark rows with lowest values. Again failed, aiming to use "EARLIER"

Thank you gor help

Re: Flag to mark two lowest values from Table. Grouped by other columns

Selva-Salimi — Mon, 21 Oct 2024 13:37:04 GMT

Hi Pawel_1990

it was not that much clear but based on my understanding you need to two worst weeks. so you can do as follows:

create a column to calculate the sum of weekly user count based on your expectation....

column sumedup_users = calculate (sum(weeklu_users) , filter (table , x= earlier x (this part should be based on what you want))

then you should rank this sumed up weekly users. you can do as follows:

column order = calculate (distinctcount( sumedup_users) , filter (table , sumedup_users < earlier (sumedup users)))

then you can use this column in your measure and add a filter to measure to have order greater than 10.

If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

Re: Flag to mark two lowest values from Table. Grouped by other columns

Anonymous — Tue, 22 Oct 2024 05:35:45 GMT

Hi Pawel_1990

Thank you very much Selva-Salimi for your prompt reply.

For your question, here is the method I provided:

Here's some dummy data

"UserActivity"

Create a measure.

Weekly_Active_Users = VAR total_Weekly_Users = SUM(UserActivity[Weekly_Users_Count]) VAR Week_Rank = RANKX( FILTER( UserActivity, UserActivity[Month_Key] = MAX(UserActivity[Month_Key]) && UserActivity[Country] = MAX(UserActivity[Country]) ), total_Weekly_Users, , DESC ) VAR Top_10_Weekly_Users = CALCULATE( SUM(UserActivity[Weekly_Users_Count]), FILTER( UserActivity, Week_Rank <= 10 ) ) RETURN DIVIDE(Top_10_Weekly_Users, 10)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.