Help on Measures to calculate daily maximum count within each month with priority in case of a tie

[LanrexTee]

Hi Team,

Pls help on the DAX measure for  below.

- I need to calculate the total maximum daily distinct count for each product within each month.

- On the day with the maximum total count for each product, I need the corresponding Quantity of the product under each shop type, i.e. only the total daily count for each product needs to be maximum, the count under each shoptype just needs to be the Quantity on that particular day.

- In case there are multiple days in the same month with ties , I need the chosen total maximum to be on the day that shopA has the highest count. For example in the final Result table below, On 9 &10-April, maximum total count is 4, I'm selecting 10-Apr count as the maximum for the month April because ShopA has higher count of 3. The same with the motorcylce which I selected 11-APril count since ShopA count is higher than on 09-Apr (w=even though both days have total max count of 5).

Sample Data Table below:

prod_name	Shoptype	Shop_name	date_val
Bicycle	shopA	a1	2024-04-09
Bicycle	shopA	a2	2024-04-09
Bicycle	shopA	a3	2024-04-11
Bicycle	shopA	a4	2024-04-11
Bicycle	shopA	a5	2024-04-10
Bicycle	shopA	a6	2024-04-10
Bicycle	shopA	a7	2024-04-10
Bicycle	shopA	a8	2024-05-08
Bicycle	ShopB	b1	2024-04-10
Bicycle	ShopB	b2	2024-04-09
Bicycle	ShopB	b3	2024-04-09
Motorcycle	shopA	a1	2024-04-09
Motorcycle	shopA	a2	2024-04-09
Motorcycle	shopA	a3	2024-04-09
Motorcycle	shopA	a4	2024-04-11
Motorcycle	shopA	a5	2024-04-11
Motorcycle	shopA	a6	2024-04-11
Motorcycle	shopA	a7	2024-04-11
Motorcycle	ShopB	b1	2024-04-09
Motorcycle	ShopB	b2	2024-04-11
Motorcycle	ShopB	b3	2024-04-09

A pivot table showing the daily count of shop_name below

My final desired result is below , I'll use a matrix visual

Result:

[Anonymous]

Hi  @LanrexTee ,

Here are the steps you can follow：

1. Create measure.

Value_Measure =
var _table=
ADDCOLUMNS(
    ALL('Table'),"Count",
    COUNTX(
    FILTER(ALL('Table'),
'Table'[prod_name]=EARLIER('Table'[prod_name])&&'Table'[Shoptype]=EARLIER('Table'[Shoptype])
    &&'Table'[date_val]=EARLIER('Table'[date_val])
    ),[Shop_name]))
var _max=
MAXX(
    FILTER(_table,[prod_name]=MAX('Table'[prod_name])&&[Month]=MAX('Table'[Month])),[Count])
var _date=
MAXX(FILTER(_table,[prod_name]=MAX('Table'[prod_name])&&[Count]=_max&&[Month]=MAX('Table'[Month])),[date_val])
return
MAXX(
    FILTER(
        _table,
        [prod_name]=MAX('Table'[prod_name])&&[Shoptype]=MAX('Table'[Shoptype])&&[Month]=MAX('Table'[Month])&&[date_val]=_date),[Count])

2. Result:

Best Regards,

Liu Yang

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

[LanrexTee]

Many thanks for the great effort Liu Yang. Possibly the sample data I provided wasn't sufficient as I tried the above but didn't work. I'm attaching the closest sample pbix with the data close to my actual data.

I tried a new measure shown in the snip below, the daily maximum count I got for each product  in each month is correct. However, if I sum up the the count displayed under each shoptype for each product in the monthly visual (the lower table), it isnt matching the actual total daily count (upper table), it is instead giving me the maximum in a month for each shoptype.

Note that I need a distinct count of the shopname each day because there'e duplicates and I dont want the shopname to be counted twice for each product in the same day.

All I need is to get the distinctcount of  (shopname) for each product  under each shoptype on the day that the total distinct count(shopname) is maximum in each month.

Pls see the link to my pbix:

https://drive.google.com/file/d/14TCLlLFUEIT9ebSACxmmAKkiedUzlR8Z/view?usp=sharing

I'm using

Count of shopname max per date_val =

MAXX(

    KEEPFILTERS(VALUES('Table'[date_val])),

    CALCULATE(DISTINCTCOUNT('Table'[shopname]))

)

[Anonymous]

Hi  @LanrexTee ,

Here are the steps you can follow：

1. Create measure.

Measure =
var _table1=
ADDCOLUMNS(
    'Table',"1",
    COUNTX(        FILTER(ALL('Table'),'Table'[prod_name]=EARLIER('Table'[prod_name])&&'Table'[Shoptype]=EARLIER('Table'[Shoptype])&&'Table'[date_val]=EARLIER('Table'[date_val])),[date_val]))
var _table2=
SUMMARIZE(
    _table1,[prod_name],[Shoptype],[date_val],
    "Count",
    MAXX(
        FILTER(_table1,[prod_name]=EARLIER([prod_name])&&[Shoptype]=EARLIER([Shoptype])),[1]))
return
MAXX(
    FILTER(_table2,[prod_name]=MAX('Table'[prod_name])&&[Shoptype]=MAX('Table'[Shoptype])),[Count])

2. Turn off – Row headers – Options – Stepped layout.

3. Result:

Here I have an understanding of the fact that ShopB is 2 on 2024.4.9, and according to the description you gave, 2 is the largest.

Best Regards,

Liu Yang

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

[LanrexTee]

Many thanks @Anonymous . However, It's not yet resolved because I need the result as shown in the table below as I mentioned previously.

That is, if u look at the pivot ale above:

- check the date which I have the maximum total daily count for each product within each month. For above maximum total Bicylce count is 4 on both 9-April and 10-Apr. Since ShopA has 3 on 10-Apr and 2 on 09-Apr, so I select April-10 as the date with the total maximum count for that month and I need to display the count values of 3 &1 ( for shopA and ShopB) respectively.

- Similarly for Motorcycle. Maximum total daily count in April is 5 (i.e. 3+2 or 4+1) which occurs on 9 and 11-Apr. But shopA count is 4 on 11-Apr (which is higher than 3 on 9-Apr), so I slect 11-Apr as the date with the maximum total daily count in Apr and I just need the values for both shops on that day which is 4 &1. 

If there's no tie, I just need the values on the day of maximum count for each product within each month.

So, the final result needs to be like below