Advanced DAX Problem - Help!

[Anonymous]

Hello and thanks for stoping by to help!

I have a data base (which is attached in Excel) that has the following columns:

Year

Quarter

Week

Country

Product Group

Product Sub-Group

Sales

Quantity

Channels

Stock

Excel DB Link 

The channels are divided into 2 categories: Stock or Not Stock.

I needed to display a calculation called Small Sales, which is basically the sum of the sales of the Not Stock channels that sold less than 25K in a given Year, Quarter, Product Group and Country PLUS the sum ALL the sales from Stock channels

For that, I have the following meassure:

Small Sales =
CALCULATE (
SUMX (
SUMMARIZE (
'DB',
'DB'[Year],
'DB'[Quarter],
'DB'[Product Group],
'DB'[Country],
'DB'[Stock],
'DB'[Channels]
),
IF (
AND ( 'DB'[Stock] = "Not Stock", [Sales] <= 25000 ),
[Sales],
IF ( 'DB'[Stock] <> "Not Stock", [Sales] )
)
)
)

Now for the complicated part...

In the power BI dashboard the user can select the Quarter and the Weeks they want to analyze

Imagine we are at Quarter 4, only 4 weeks deep in the Quarter
Almost every client would not have reached the 25K threshold, so their Sales would be considered as Small Sales, but as the Quarter progresses, some will, and those sales would be removed.

Users would have a false picture of how the Small Sales meassure will propably evolve thoughout the Quarter

So to mittigate this, I need to analyze which channels have reached that threshold of 25K in the past 3 Quarters (the Big Channels), and exclude them from the Small Sales calculation UNTIL all the weeks of the Quarter (13 weeks) are selected.

Then, I need to make a slicer so the users can select if then want to see the Small Sales with or without the Big Channels...

I have NO clue how to do this, I'm totally lost, please help me!

[lbendlin]

Can you please explain this part?

CALCULATE (
SUMX (
SUMMARIZE (

There's no filter modifier so CALCULATE() seems unnecessary.  SUMMARIZE should be replaced by GROUPBY.

IF (
AND ( 'DB'[Stock] = "Not Stock", [Sales] <= 25000 ),
[Sales],
IF ( 'DB'[Stock] <> "Not Stock", [Sales] )

can be rewritten as 

IF ( 'DB'[Stock] <> "Not Stock" || [Sales] <= 25000 ),
[Sales])

Can you show  an example of your week 4 scenario? Can you provide sample data that covers the issue?

[Anonymous]

Thanks for answering!
Also thanks for improving the Small Sales formula! (can you please explain me why group by instead of Summarize?)

So for the example, I'll attach an Excel file:
In the picture bellow, I am looking at Chanel171 sales in Q1 of 2021. It is not a Stock Chanel.

So until week 8, this chanel sales would clasiffy as Small Sales, but since week 9 where the 25K thresold was surpased, this chanel sales would not clasiffy as Small Sales.

This happens with a lot of chanels.

I need a way to automatically detect what chanels have sold more than 25K in the las 3 Quarters in a row, so I can exclude them from the Small Sales calculation. I imagine a slicer where I can select wheter I want to see All Chanels, Big Chanels or Small Chanels.

This way, when all the weeks of a Quarter are selected (all 13 Weeks of a Quarter), I can choose to include All Chanels in the Small Sales calulation, but if not all weeks are selected (lets supose only 6 weeks are available right now), I can choose to only include the Small Chanels, because the Big Chanels sales will propably end up being more than 25K, hence, excluded for the Small Sales calculation.

Here is the Link to the Excel file (it has the data base as well as the example):
https://docs.google.com/spreadsheets/d/1APFcoyC9K34FG2JHXriQbIv3ufvHhQ4k/edit?usp=sharing&ouid=11352...

Thanks in advance!

[lbendlin]

So you want to allow your users to move the goal posts and influence when the cumulative $25K threshold hits?  That is possible but I'm not sure it makes for much of a truthful report.

All the secrets of SUMMARIZE - SQLBI

[Anonymous]

I want to classify Chanels in 2: Big Chanels and Small Chanels

Then, I want to have a slicer to filter by them

Big Chanels: Sold >=25K for the last 3 Quarters in a row
Small Chanels: the opposite

[Anonymous]

I want to prevent to see a drop on the Small Sales (Sales of chanels <25K in a Q).

How?
By being able to filter out the chanels that have consistently sold more than 25K in the past Quarters.
E.g.:
Chanel 171 sales would clasiffy as Small Sales untill week 8, but when is week 9, the 25K thresold is surpased, so this chanel sales would not clasiffy as Small Sales anymore.

I want to prevent the users to see an inflated number of Small Sales by filtering out the Big Chanels until all the Quarter info can be accesed or selected.

[rsbin]

@Anonymous,

I'm just thinking out loud here, but my first approach would be to create another Summary Table of Completed Year, Completed Quarter, Channel, TotalSales, then the resulting Classification. 

Then a calculated column for Current Quarter Status - i.e. if 3 Quarters in a row are true, then Big Channel, else Small Channel. 

The idea here is to break down your complex problem into smaller bits.  Might not be the most efficient, but it may provide additional clarity as you break this down into smaller bites.

Regards,

[Anonymous]

Ok, can you please explain how would you do that? I'm not an expert on PBI!

Thanks!

[rsbin]

@Anonymous,

I am by no means an expert in PBI either.  But here are some further thoughts.

I would start by creating a new calculated column in the 'DB' table to concatenate Year-Quarter.

i.e. 2021-Q1, 2021-Q2, 2021-Q3.

QuarterlySummary = SUMMARIZE (DB, [ProductGroup], [Product],[Country], [Year-Quarter],  "Sales", SUM( DB[Sales] ))

I am not good at writing DAX on the fly, so you will probably need to adjust this code to fit your specific requirements for grouping.  This will then give you a picture of all Products that achieve the $25000 threshold.   Then another calculated column with a conditional IF or SWITCH statement that checks for the previous 3 quarters.  And then take it step by step from here.  Hope this suggestion works for you.

Best Regards,