Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi everyone, I hope you are great.
Getting to the point, I have this dataset:
YEAR MONTH PRODUCT_NAME SALES
2018 January Black Jeans 100
2018 January Blue Jeans 200
2018 January White Jeans 300
2019 January Black Jeans 200
2019 January Blue Jeans 150
2019 January White Jeans 500
I need to know the quantity of products that have increased their sales.
In this example dataset, it'd be:
QuantityWIncreasedSales = 2
Black Jeans 100 -> 200
* Increase of 100 $
* Increase of 100%
White Jeans 300 -> 500
*Increase of 200$
*Increase of 67%
Solved! Go to Solution.
Hi @deloitte_jogonz ,
I've created a sample as your requested, then add below measures to generate the results:
Increase% = var a = CALCULATE(MAX('Table'[ SALES]),FILTER(ALL('Table'),[YEAR]=2018),VALUES('Table'[MONTH]),VALUES('Table'[PRODUCT_NAME])) var b = CALCULATE(MAX('Table'[ SALES]),FILTER(ALL('Table'),[YEAR]=2019),VALUES('Table'[MONTH]),VALUES('Table'[PRODUCT_NAME])) var c= CALCULATE(b-a,ALL('Table')) Return IF(c>=0,DIVIDE(c,a),BLANK())
QuantityWIncreasedSales = CALCULATE(DISTINCTCOUNT('Table'[PRODUCT_NAME]),FILTER('Table',[Increase%]<>BLANK()))
Pbix attached here:
Best regards,
Dina Ye
Hi @deloitte_jogonz ,
I've created a sample as your requested, then add below measures to generate the results:
Increase% = var a = CALCULATE(MAX('Table'[ SALES]),FILTER(ALL('Table'),[YEAR]=2018),VALUES('Table'[MONTH]),VALUES('Table'[PRODUCT_NAME])) var b = CALCULATE(MAX('Table'[ SALES]),FILTER(ALL('Table'),[YEAR]=2019),VALUES('Table'[MONTH]),VALUES('Table'[PRODUCT_NAME])) var c= CALCULATE(b-a,ALL('Table')) Return IF(c>=0,DIVIDE(c,a),BLANK())
QuantityWIncreasedSales = CALCULATE(DISTINCTCOUNT('Table'[PRODUCT_NAME]),FILTER('Table',[Increase%]<>BLANK()))
Pbix attached here:
Best regards,
Dina Ye