First Product Bought by Product category with greatest revenue

[jameshoneywill]

Hi 

There are lots of posts very simular but I havent found any that nail this one down, 

I am trying to find the product category which has the biggest sales on a customers first bill. 

This measure works however I cant get it to sum by the parent category, the results it provides are calculated by sub category (there are many sub categories under each parent category and it's giving me the largest of the sub categories which is not correct where a customer has a big purchase of one sub category but they have lots of medium purchase of a different category where the total would be more...)

Is there a simple fix to force parent category to this code?

p.s. if anyones feels mega flash and wants to tell me how to replicate / adjust the measure to output the second product bought (return blank if its the same as the first) that would save me another post!)

TIA

1st ParentCategory purchased with greatest MRR =

MAXX(

    TOPN(1,

    SUMMARIZE(fact_sales, dim_calendar[Date],fact_sales[MRR], dim_product[ParentCategory]),

        CALCULATE(min (dim_calendar[Date])), asc,
        CALCULATE(MAX(fact_sales[MRR]),dim_calendar[Date]),DESC)
, dim_product[ParentCategory])

[rajendraongole1]

Hi @jameshoneywill - I have adjust your measure to aggregate the sales by the parent category instead of the subcategory.

First ParentCategory Purchased with Greatest MRR =
VAR FirstBillDate =
CALCULATE(
MIN(dim_calendar[Date]),
ALLEXCEPT(fact_sales, fact_sales[CustomerID])
)

VAR SalesOnFirstBill =
CALCULATETABLE(
SUMMARIZE(
fact_sales,
dim_product[ParentCategory],
"TotalMRR", SUM(fact_sales[MRR])
),
dim_calendar[Date] = FirstBillDate
)

RETURN
MAXX(
TOPN(
1,
SalesOnFirstBill,
[TotalMRR],
DESC
),
dim_product[ParentCategory]
)

you can correctly identify the parent category with the largest sales on the first bill

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[jameshoneywill]

Hi @rajendraongole1 , 

Thank you, this is exceptionally close but unfortuantly does't work, 

I receive a completely blank result (no errors just blank) on your post, but,
I am able to return values by changing this;
VAR FirstBillDate =
CALCULATE(
MIN(dim_calendar[Date]),
ALLEXCEPT(fact_sales, fact_sales[CustomerID])
)
for this;
VAR FirstBillDate =
CALCULATE(
MIN(dim_calendar[Date]),
ALLEXCEPT(dim_customers, dim_customers[CustomerID])
)

After this change it only works for customers who were billing at the start of my data (April 2019), so if a customer started billing in May 2019 they are not returned in the data... 

Any ideas? 
TIA