cancel
Showing results for
Did you mean:
Helper I

## Virtual Table to classify Chanels

Hello!

I have a data base with the following columns (also attached in this link: DB PBI )

 Year Quarter Week Country Product Group Product Sub-Group Sales Quantity Channels Stock

I need to classify the chanels in 2: Big chanels and Small chanels

Big Chanels sold more than 25K in the past 3 Quarters

Small Chanels sold less than 25K in the past 3 Quarter

I need to create a slicer to filter my report by Big Chanels or Small Chanels

How can I do it?

1 ACCEPTED SOLUTION
Community Support

Hi @andresloji ,

The following output is like your xlsx,but not roll up:

And if you need roll up,see the below:

Import data ,then create change many column form text to whole number ,then create the below column ,and measure:

``sumsale = CALCULATE(SUM(DB1[Sales]),FILTER(ALL(DB1),DB1[Year]=MAX(DB1[Year])&&DB1[Quarter]=MAX(DB1[Quarter])&&DB1[Channels]=MAX(DB1[Channels])&&DB1[Stock]="Not Stock"))``

column:

``````sumsale1 =
CALCULATE (
SUM ( DB1[Sales] ),
FILTER (
ALL ( DB1 ),
DB1[Year] = EARLIER ( DB1[Year] )
&& DB1[Quarter] = EARLIER ( DB1[Quarter] )
&& DB1[Channels] = EARLIER ( DB1[Channels] )
&& DB1[Stock] = "Not Stock"
)
)``````

measure:

``````Channel =
IF (
MAX ( DB1[sumsale1] ) >= 25000
&& CALCULATE (
MAX ( DB1[sumsale1] ),
FILTER (
ALL ( DB1 ),
DB1[Channels] = MAX ( DB1[Channels] )
&& IF (
MAX ( DB1[Quarter] ) >= 2,
DB1[Quarter]
= MAX ( DB1[Quarter] ) - 1
&& DB1[Year] = MAX ( DB1[Year] ),
DB1[Quarter] = 4
&& DB1[Year]
= MAX ( DB1[Year] ) - 1
)
)
) >= 25000
&& CALCULATE (
MAX ( DB1[sumsale1] ),
FILTER (
ALL ( DB1 ),
DB1[Channels] = MAX ( DB1[Channels] )
&& IF (
MAX ( DB1[Quarter] ) >= 3,
DB1[Quarter]
= MAX ( DB1[Quarter] ) - 2
&& DB1[Year] = MAX ( DB1[Year] ),
IF (
MAX ( DB1[Quarter] ) = 2,
DB1[Quarter] = 4
&& DB1[Year]
= MAX ( DB1[Year] ) - 1,
DB1[Quarter] = 3
&& DB1[Year]
= MAX ( DB1[Year] ) - 1
)
)
)
) >= 25000,
"Big Channel",
"Small channel"
)``````

Best Regards

Lucien

4 REPLIES 4
Community Support

Hi @andresloji ,

The following output is like your xlsx,but not roll up:

And if you need roll up,see the below:

Import data ,then create change many column form text to whole number ,then create the below column ,and measure:

``sumsale = CALCULATE(SUM(DB1[Sales]),FILTER(ALL(DB1),DB1[Year]=MAX(DB1[Year])&&DB1[Quarter]=MAX(DB1[Quarter])&&DB1[Channels]=MAX(DB1[Channels])&&DB1[Stock]="Not Stock"))``

column:

``````sumsale1 =
CALCULATE (
SUM ( DB1[Sales] ),
FILTER (
ALL ( DB1 ),
DB1[Year] = EARLIER ( DB1[Year] )
&& DB1[Quarter] = EARLIER ( DB1[Quarter] )
&& DB1[Channels] = EARLIER ( DB1[Channels] )
&& DB1[Stock] = "Not Stock"
)
)``````

measure:

``````Channel =
IF (
MAX ( DB1[sumsale1] ) >= 25000
&& CALCULATE (
MAX ( DB1[sumsale1] ),
FILTER (
ALL ( DB1 ),
DB1[Channels] = MAX ( DB1[Channels] )
&& IF (
MAX ( DB1[Quarter] ) >= 2,
DB1[Quarter]
= MAX ( DB1[Quarter] ) - 1
&& DB1[Year] = MAX ( DB1[Year] ),
DB1[Quarter] = 4
&& DB1[Year]
= MAX ( DB1[Year] ) - 1
)
)
) >= 25000
&& CALCULATE (
MAX ( DB1[sumsale1] ),
FILTER (
ALL ( DB1 ),
DB1[Channels] = MAX ( DB1[Channels] )
&& IF (
MAX ( DB1[Quarter] ) >= 3,
DB1[Quarter]
= MAX ( DB1[Quarter] ) - 2
&& DB1[Year] = MAX ( DB1[Year] ),
IF (
MAX ( DB1[Quarter] ) = 2,
DB1[Quarter] = 4
&& DB1[Year]
= MAX ( DB1[Year] ) - 1,
DB1[Quarter] = 3
&& DB1[Year]
= MAX ( DB1[Year] ) - 1
)
)
)
) >= 25000,
"Big Channel",
"Small channel"
)``````

Best Regards

Lucien

Helper I

That is genius, thank you very much!

Helper I

Hello @VahidDM , I don't understand what the expected output is, can you explain me how to get it?

Super User

Can you post the Expected output from your sample data?

Announcements

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

#### Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!