Count the values greater than the average value

[Linhle]

Dear all,

Currently I'm struggling a bit and I hope someone can help me with the following.

My scenario is that I'm tracking action of the customer (System_id) on the product. Then, I've got a table as below:

Date	user_id	Total_action	Unique_user	Product	Weekday	System_id
3/1/2021	101759	1	1	r3	2	2996
3/2/2021	106055	2	1	r3	2	1059
3/2/2021	101759	2	1	r3	2	2846
3/1/2021	112181	1	1	w	2	3231

* One system_id has many user_id

For more information, Total_action is the number of times the user is active on the product. User_id represents each user and 

==> Here is my sample data: https://drive.google.com/drive/u/0/folders/1QydivQAdNOIcjRGK7frJqRbuLCHZopej

I need to produce visualizations like this:

Product	Average action per day	Maximum number	Effective System_id number
b
m
p
r1
r2
r3
w
w1
w2

In deatail,

+ Average action per day is the average number of times a user active on the product per day. For example, average action per day of r3 = (1+2+2)/2/2=1.25

+ Maximum number is the maximum number of times the user is active on the product in a period of time. For example, Maximum number of r3 from 3/1/2021 to 3/2/2021 is 3 (=1+2)

+ Effective System_id number is the number of System_id whose average action per day of each system is greater than Average action per day . For example, effective System_id number is 1 (system_id 101759). 

This measure of is Average action per day as follows: 

Average action per day= SUM(Total[Total_action])/DISTINCTCOUNT(Total[user_id])/[Datediff]

Datediff = CALCULATE(SUM('Date'[work day or not]),DATESBETWEEN('Date'[Date],FIRSTDATE(total[Date]),LASTDATE(total[Date])))

I can't find solution for other measures: Maximum number and Effective System_id number.

Please help me to solve this problem!

Thanks,

Linh

[v-shex-msft · Accepted solution]

Hi @Linhle,

You can create a variable for the first-level summary then use the iterator function to apply second aggerates.

Average action per day=
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( Table ),
        [Product],
        [Date],
        "Total", SUM ( Table[Total_action] )
    )
RETURN
    AVERAGEX ( summary, [Total] )

Maximum number =
CALCULATE (
    SUM ( Table[Total_action] ),
    ALLSELECTED ( Table ),
    VALUES ( Table[Product] )
)

Effective System_id number =
VAR ProductGroup =
    SUMMARIZE (
        ALLSELECTED ( Table ),
        [Product],
        [Date],
        "Total", SUM ( Table[Total_action] )
    )
VAR SystemGroup =
    SUMMARIZE (
        ALLSELECTED ( Table ),
        [System_id number],
        [Date],
        "Total", SUM ( Table[Total_action] )
    )
RETURN
    IF (
        AVERAGEX ( SystemGroup, [Total] ) > AVERAGEX ( ProductGroup, [Total] ),
        "Y",
        "N"
    )

The blog about multiple aggregations on measure calculations:

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[Jihwan_Kim]

Hi, @Linhle 

Please check the link down below.

I am not sure whether I fully understand your question.

All measures are in the sample pbix file, and all steps are numbered in front of each measure.

https://www.dropbox.com/s/efjow0kb7xstb27/lll.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

I got the same problems 😞

Looking for helps