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

[Pawel_1990]

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

[Anonymous · Accepted solution]

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.

[Selva-Salimi]

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.