Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Thanks in advance!
Solved! Go to Solution.
Hi @Anonymous ,
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"
)
And you could download my pbix file if you need!
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
Hi @Anonymous ,
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"
)
And you could download my pbix file if you need!
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
That is genius, thank you very much!
Hello @VahidDM , I don't understand what the expected output is, can you explain me how to get it?
Hi @Anonymous
Can you post the Expected output from your sample data?
Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |