Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi all,
I have sales data in of different agricultural products with sales in SALES AMOUNT, which is as following in the picture.
Zone | State | Depot | District | Crop | Product | Sales Amount |
NZ | HP | Shimla | Shimla | Wheat | Gol | 12 |
CZ | MP | Gwalior | Gwalior | Wheat | Gol | 12 |
SZ | AP | Nellore | Nellore | Chilly | Jwala | 55 |
SZ | AP | Nellore | Nellore | Rice | 5566 | 55 |
SZ | AP | Nellore | Nellore | Rice | 5566 | 55 |
SZ | AP | Warangal | Warangal | Rice | 5566 | 55 |
NZ | HP | Shimla | Shimla | Chilly | Jwala | 10 |
CZ | MP | Gwalior | Gwalior | Wheat | Gol | 10 |
SZ | AP | Nellore | Nellore | Rice | 5566 | 55 |
SZ | AP | Nellore | Nellore | Chilly | Jwala | 55 |
Now when I create Pivot table of the above considering the columns in EXCEL between PRODUCT and SALES AMOUNT, the result is as follows
Year | |||||||||||||||||
Product | 2016 | 2015 | 2014 | 2013 | 2012 | 2011 | 2010 | 2009 | 2008 | 2007 | 2006 | 2005 | 2004 | 2003 | 2002 | 2001 | Grand Total |
Jwala | 10 | 10 | 10 | 30 | |||||||||||||
Gol | 17 | 10 | 10 | 37 | |||||||||||||
7029 | 10 | 10 | |||||||||||||||
5566 | 10 | 10 | 20 | 20 | 55 | 22 | 10 | 10 | 157 | ||||||||
2288 | 10 | 10 | |||||||||||||||
1121 | 10 | 10 | 20 | ||||||||||||||
Grand Total | 27 | 10 | 20 | 30 | 10 | 55 | 22 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 264 |
I wish to know the Product Life cycle, The conditions for the PLC are as follows:
This report will show summation of above data intp three categories Launch, Peak and Post-peak. |
Considering reference year as X, any product sold for the first time in X or X-1 or X-2 will be considered as "Launch product". |
Similarly any product sold for the first time in X-6 or X-5 or X-4 or X-3 will be considered as "Peak Product" |
And, Any product sold for the fist time in X-7 or earlier will be considered as "Post-peak Product" |
The categorization of a product is dynamic in nature and will depend on the year when the report is run |
Now considering the reference year is 2016, I wish to know the sales revenue from each of the "LAUNCH, PEAK, POST Peak" periods in the year 2016,
hence it will be as follows
The above I have done manually in excel. I wish that these calculations to be done by the program in Power BI when I choose the date/ year slicer and consequently make graphs based on the values( 1. Pie chart on Launch, Peak, Post peak, 2. PLC curve to show the products on the curve.)
I dont know how it can be achieved.
Solved! Go to Solution.
Hi @Sagarkansal
Try the following steps, I created for Launch alone.
1. Create a summary table using
Launch = SUMMARIZE(Sales,Sales[Product], "FirstSold",Min(Sales[Year]))
The table name will be called Launch with column names Product and FirstSold
Link the product from sales fact and product from this table
2. Create a column called FirstSold in the sales fact table
as FirstSold = Related(Launch[FirstSold])
3. Create a year Table which contains the years from 2001 to 2016 and Year as a column name.
Do no link this to your SalesFact Table.
4. Create a measure called
Launched Products = Calculate(
( distinctcount(Launch[Product])),
FILTER(Launch,
COUNTROWS(FILTER(VALUES(Year[Year]),
Launch[FirstSold] >= ('Year'[Year]) - 2 &&
Launch[FirstSold] <= ('Year'[Year])
))
> 0)
)
5. Create a measure SumSales = sum(Salesfact[Sales])
6 Create the measure
LaunchedProductSales = Calculate(
( [SumSales] ),
FILTER(Sales,
COUNTROWS(FILTER(VALUES(Year[Year]),
Sales[Year] >= ('Year'[Year]) - 2 &&
Sales[Year] <= ('Year'[Year])
))
> 0)
)
7. Display the measures using the card visulaisation and it should match your data.
8. Create similarly the measures for PeakProducts and Post-Peak Products by altering the year comparison condition.
9. Below is a screen shot of the output with your data posted.
If this works for you please accept this as a solution and also give KUDOS.
Cheers
CheenuSing
Hi @Sagarkansal
Get Set and GO!!!!!!!!!!!!!
The steps file is uploaded as word document at https://1drv.ms/w/s!ApP3mBZyGaHfgRhcMywbLSFj1vXS
and the pbix file https://1drv.ms/u/s!ApP3mBZyGaHfgRoDyFexv0L1JlkY
I will send you a private message for how to give KUDOS. Hey do not forget to give KUDOS to this post.
Cheers
CheenuSing
Hi @Sagarkansal
Try the following steps, I created for Launch alone.
1. Create a summary table using
Launch = SUMMARIZE(Sales,Sales[Product], "FirstSold",Min(Sales[Year]))
The table name will be called Launch with column names Product and FirstSold
Link the product from sales fact and product from this table
2. Create a column called FirstSold in the sales fact table
as FirstSold = Related(Launch[FirstSold])
3. Create a year Table which contains the years from 2001 to 2016 and Year as a column name.
Do no link this to your SalesFact Table.
4. Create a measure called
Launched Products = Calculate(
( distinctcount(Launch[Product])),
FILTER(Launch,
COUNTROWS(FILTER(VALUES(Year[Year]),
Launch[FirstSold] >= ('Year'[Year]) - 2 &&
Launch[FirstSold] <= ('Year'[Year])
))
> 0)
)
5. Create a measure SumSales = sum(Salesfact[Sales])
6 Create the measure
LaunchedProductSales = Calculate(
( [SumSales] ),
FILTER(Sales,
COUNTROWS(FILTER(VALUES(Year[Year]),
Sales[Year] >= ('Year'[Year]) - 2 &&
Sales[Year] <= ('Year'[Year])
))
> 0)
)
7. Display the measures using the card visulaisation and it should match your data.
8. Create similarly the measures for PeakProducts and Post-Peak Products by altering the year comparison condition.
9. Below is a screen shot of the output with your data posted.
If this works for you please accept this as a solution and also give KUDOS.
Cheers
CheenuSing
Hi @CheenuSing The year table needs to be dynamic, not static( not only till 2016 but updated automatically accordingly to sales date , which means it will change each year and 2016 was only a reference year. We could have taken 2008 as reference year also and next year 2017 can be reference year
Hi @CheenuSing
there are 2 error shown when I create measure for Launch Products. WHen the syntax is
1. Launched Products = Launch = (CALCULATE(DISTINCTCOUNT(Launch[Product]),FILTER.........
i.e, when there is no closing bracket in Calculate formula after (Launch[Product]), then PBI says the rror is
ERROR: The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
2. When the syntax is as you said as:
Launched Products = Launch = (CALCULATE(DISTINCTCOUNT(Launch[Product])),FILTER
the error shown is as follows:
The syntax for ')' is incorrect. (DAX(Launch = (CALCULATE(DISTINCTCOUNT(Launch[Product])),FILTER(Launch,COUNTROWS(FILTER(VALUES('YEAR'[Year]),Launch[FirstSold]>=('YEAR'[Year])-2&&Launch[FirstSold]<='YEAR'[Year]))>0))))).
Hi @Sagarkansal
It should be syntax issue .
The original formula I gave was
Launched Products = Calculate(
( distinctcount(Launch[Product])),
FILTER(Launch,
COUNTROWS(FILTER(VALUES(Year[Year]),
Launch[FirstSold] >= ('Year'[Year]) - 2 &&
Launch[FirstSold] <= ('Year'[Year])
))
> 0)
)
It is not like Launched Products = Launch = Calculate ( .....
Please check.
As for how it will work with other dimensions, it depnds on the data model. Share the full data model.
Cheers
Cheenusing
@CheenuSing....
1. PLease find below the full data model. Here YEAR COLUMN is just the YEAR() value of the date column.
2. The PBI should return the Sum of sales value from REFERENCE Year only, following the condition that the product was launched only within the date range according to PLC terms as of Launch, PEak, Post peak periods.
District | Crop | HY_Var | Product | SKU | Date | Year | Sales Volume | Sales Amount | Rebates_Disc | Net Amount | NRV | FLTC | Gross Contribution | Marketing Overhead | Net Margin |
Shimla | Wheat | HY | Gol | Dhalli | 10-Jun-16 | 2016 | 10 | 12 | 3 | 9 | 0.9 | 0.45 | 0.5 | 0.09 | 0.36 |
Gwalior | Wheat | HY | Gol | Gwalior | 12-Oct-16 | 2016 | 20 | 12 | 3 | 9 | 0.5 | 0.225 | 0.2 | 0.045 | 0.18 |
Nellore | Chilly | Var | Jwala | XYZ | 29-Dec-11 | 2011 | 22 | 55 | 13.75 | 41.25 | 1.9 | 0.9375 | 0.9 | 0.1875 | 0.75 |
Nellore | Rice | HY | 5566 | XYZ | 13-Oct-10 | 2010 | 22 | 55 | 13.75 | 41.25 | 1.9 | 0.9375 | 0.9 | 0.1875 | 0.75 |
Nellore | Rice | HY | 5566 | XYZ | 13-Oct-14 | 2014 | 22 | 55 | 13.75 | 41.25 | 1.9 | 0.9375 | 0.9 | 0.1875 | 0.75 |
Warangal | Rice | HY | 5566 | XYZ | 13-Oct-13 | 2013 | 22 | 55 | 13.75 | 41.25 | 1.9 | 0.9375 | 0.9 | 0.1875 | 0.75 |
Shimla | Chilly | Var | Jwala | Dhalli | 10-Jun-12 | 2012 | 22 | 10 | 2.5 | 7.5 | 0.3 | 0.170455 | 0.2 | 0.034090909 | 0.14 |
Gwalior | Wheat | HY | Gol | Gwalior | 12-Oct-13 | 2013 | 22 | 10 | 2.5 | 7.5 | 0.3 | 0.170455 | 0.2 | 0.034090909 | 0.14 |
Nellore | Rice | HY | 5566 | XYZ | 29-Dec-14 | 2014 | 22 | 55 | 13.75 | 41.25 | 1.9 | 0.9375 | 0.9 | 0.1875 | 0.75 |
Nellore | Chilly | Var | Jwala | XYZ | 13-Oct-15 | 2015 | 22 | 55 | 13.75 | 41.25 | 1.9 | 0.9375 | 0.9 | 0.1875 | 0.75 |
Nellore | Rice | HY | 5566 | XYZ | 13-Oct-16 | 2016 | 22 | 55 | 13.75 | 41.25 | 1.9 | 0.9375 | 0.9 | 0.1875 | 0.75 |
Warangal | Rice | HY | 5566 | XYZ | 13-Oct-13 | 2013 | 22 | 55 | 13.75 | 41.25 | 1.9 | 0.9375 | 0.9 | 0.1875 | 0.75 |
Warangal | Rice | HY | 1121 | XYZ | 13-Oct-14 | 2009 | 22 | 55 | 13.75 | 41.25 | 1.9 | 0.9375 | 0.9 | 0.1875 | 0.75 |
Shimla | Wheat | HY | Gol | Dhalli | 13-Oct-13 | 2008 | 22 | 10 | 2.5 | 7.5 | 0.3 | 0.170455 | 0.2 | 0.034090909 | 0.14 |
Gwalior | Wheat | HY | 2288 | Gwalior | 10-Jun-12 | 2007 | 22 | 10 | 2.5 | 7.5 | 0.3 | 0.170455 | 0.2 | 0.034090909 | 0.14 |
Nellore | Chilly | Var | Jwala | XYZ | 12-Oct-13 | 2006 | 22 | 55 | 13.75 | 41.25 | 1.9 | 0.9375 | 0.9 | 0.1875 | 0.75 |
Nellore | Chilly | Var | Jwala | XYZ | 29-Dec-14 | 2005 | 22 | 55 | 13.75 | 41.25 | 1.9 | 0.9375 | 0.9 | 0.1875 | 0.75 |
Nellore | Chilly | Var | Jwala | XYZ | 13-Oct-15 | 2004 | 22 | 55 | 13.75 | 41.25 | 1.9 | 0.9375 | 0.9 | 0.1875 | 0.75 |
Nellore | Rice | HY | 5566 | XYZ | 13-Oct-16 | 2003 | 22 | 55 | 13.75 | 41.25 | 1.9 | 0.9375 | 0.9 | 0.1875 | 0.75 |
Nellore | Rice | HY | 5566 | XYZ | 13-Oct-13 | 2002 | 22 | 55 | 13.75 | 41.25 | 1.9 | 0.9375 | 0.9 | 0.1875 | 0.75 |
Warangal | Chilly | Var | Jwala | XYZ | 13-Oct-13 | 2001 | 22 | 55 | 13.75 | 41.25 | 1.9 | 0.9375 | 0.9 | 0.1875 | 0.75 |
@CheenuSing The Above PIE chart, TILE Charts were obtained after coding according to your steps.
There are 2 major issues that are faced now:
1. The Sums of sales amount shouldreturn from the sales amount of REFERENCE YEAR only. Whereas, PBI is summing the sales amount of the previous years from reference year, within year range of the Launch, Peak and post peak periods (which is not required).
2. If I am working on INDIVIDUAL PRODUCTS
The product can be only in one stage, EIther LAunch, PEAK or Post PEAK (not all the 3) with reference to the REFERENCE year in its PLC. How can we achieve that, considering the following:
a. If product was introduced before 7 years with respect to the REFERENCE year, i.e is in POST peak period currently, then the visualizations of Peak and LAunch category should not be shown
b. If product was introduced between 3-4 years earlier to REFERENCE year, ie product is in PEak period currently, It should not show its vizualization of Post peak and Launch
c. If product was introduced within 3 years from reference year, ie product is in Launch period, it shouldnot show its vizualizations of POST PEAK and PEAK periods
3. If I am working on whole year basis, considereing all the products, then the 3 segments Launch, Peak, Post peak can be shown.
How to show both the points 2 & 3 in PBi and switch between the two whne required in vizualizations on YEar and Product level
For reference year, eg. 2016
Launch category sales: As no product was launched in 2016-14(3 years), hence, any contribution from these year to sales of 2016 is not there, hence LAunch product category value is 0
PEAK Category sales: As products 1 and 4 were launched in the range of years 2013-2010 (4 years), hence the products are currently in Peak period of PLC, hence thier sales amount will be the sales amount from YEAR 2016 NOT sum of sales in period 2013 -2010. Hence, PEak period sales is 8+7=15 ,ie. sum of the sales in year 2016
Post Peak category sales: will be sales amount of the remaining products which were sold earlier than 2010, and the sales values will be from year 2016(reference year), i.e 0+0+8+8+13+14+16= 59
Similarly if we would consider year 2010 as reference year,
Launch category sales: will be considered if the products were launched in the period 2010-2008 (3 years), and the sales amount will be considered of year 2010 ie. 9+11+8+10+11+13=52
Peak category sales: will be considered if the products were launched in the period 2007-2004 ( years), but sales before 2006 are not given hence we will consider all sales in peak period sales, and and the sales amount will be considered of year 2010 ie. 13+16+16=45
POST PEAK CATEGORY SALES: As no product was introduced before 2004, hence no sales amount for 2010, from the year 2010 will be considered.
Hi @Sagarkansal
I have solved your problem. But before I send you I need the data table in excel as shown in the picture uploaded to one drive and share the link.
It is midnight now in Singapore.
Cheers
CheenuSing
@CheenuSing Below is the link for the file on Onedrive.
https://1drv.ms/x/s!AvqPBdmOr62IhEhI84YL0G6s1kVl
Kindly share the link of file here.
Also share the Step wise procedure.
How can I give you kudos? Please tell
Hi @Sagarkansal
Get Set and GO!!!!!!!!!!!!!
The steps file is uploaded as word document at https://1drv.ms/w/s!ApP3mBZyGaHfgRhcMywbLSFj1vXS
and the pbix file https://1drv.ms/u/s!ApP3mBZyGaHfgRoDyFexv0L1JlkY
I will send you a private message for how to give KUDOS. Hey do not forget to give KUDOS to this post.
Cheers
CheenuSing
Hi!
I've tried having a look at the files, since I find myself needing to have PLC chart, but the files were not available.
Could you confirm that they are not to be available?
Cheers,
Antonio
hi @Sagarkansal
I do not have them now. So sorry about that.
Cheers
CheenuSIng
Dear @CheenuSing Ji.... I am uploading the PBIX and EXCEL files here. Please go through it.
I tried to copy the Launch, Peak and post peak to the PBIX file I had last from you. Following is the link for EXCEL and PBIX files:
https://1drv.ms/f/s!AvqPBdmOr62IhnuhN6KN7rTlCNK_
In PBIX,
a. I wish to filter the First Sold list basede on the year selected (Sales_Data[CY]), which is not filtering. CY means calendar year
b. Upon selecting a CY, I wish to show in 3 different lists, which Products are currently in Launch year, Peak Year or post peak year. No one product can repeat in any other list. (e.g If One product is in Launch year, it cannot be in Peak or post peak year for that CY selected year)
c. Whean you look at the Edit Queries table, e.g LaunchProduct yes, then some of the products do not follow the logic to display 1. In some rows, we can see that Launch, Peak and Post peak yesses have 0 as value, whcih is not possible.
Dear @CheenuSing ... In excel file you may find that last 30-40 rows have only Product Value, Crop value and year values.....Those rows are for determining the exact First sold year for a product and needs to be retained.
Hi @Sagarkansal
I went through your file . I found that the calculated column
LaunchYES = IF([CY]=[FirstSoldCy]&&[CY]-[FirstSoldCy]<=2,1,0) , is wrong.
The logic says if CY is FirstSoldCY and also (CY - FirstSoldCY) <= 2 .
Say CY = 2017 and firstsoldCY=2016 then the above condition fails and returns 0, where as it should be 1.
I changed the formula as
NewLaunchYES = IF([CY]-[FirstSoldCy]<=2,1,0)
With this
Say CY = 2017 and firstsoldCY=2016 then the above condition will retrun 1.
Try changing your LaunchYes as defined for NewLaunchYES and you should get the right results.
Do get back to me if you need further clarification.
Cheers
CheenuSing
Dear @CheenuSing....I wish to attain the following 2 points as well
a. In the bottom right window of Power BI, there is a list which shows the product name and First Sold year. I wish to filter the list based on the year selected , which is not filtering. CY means calendar year
b. Upon selecting a CY, I wish to show in 3 different lists, which Products are currently in Launch year, Peak Year or post peak year. No one product can repeat in any other list. (e.g If One product is in Launch year, it cannot be in Peak or post peak year for that CY selected year)
Dear @CheenuSing..... I have achieved the first point (a. to filter the first sold list)
Help me in showing the products/product name according to 3 list of Launch, peak and post peak year products name.
Hi @Sagarkansal
This is quite straight forward. Create a table with Product from Sales_Data.
For Launched Products set the visual filter LaunchYes = 1
For PeakProduct Sales set the visual filter PeakYes = 1
For PostPeakProduct Sales set the viausl filter PostPeakYes = 1
Cheers
CheenuSing
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.