Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hello everyone!
I have made this subset of data from a larger set of data.
Business Case: I want to track which products have the highest increase in terms of cost so that I can ask my distributor and negotiate with them. This is also a way to know where my money goes and if it is spent effectively.
So my main goal for this dashboard is to:
What I have right now is a slicer with a start date and end date. The data changes daily. So the computation for the Top would take the earliest date and end date of my slicer.
Hope this makes sense.
About the data
DC (Text) : refers to the distributor
Delivery Date (Date) : the date the product was delivered
Product Description (Date) : Name of the product
Standard Pack Size (Text): Unit of measure of the product
Cost Per Case (Decimal): Cost of the product
DC | Delivery Date | Product Description | Standard Pack Size | Cost Per Case |
VitCo | 01/03/2022 | Half & Half | 12 : 32 Oz | $ 23.00 |
VitCo | 01/03/2022 | Whipping Heavy Cream 40% | 6 : 1/2 Gal | $ 40.34 |
VitCo | 01/03/2022 | Sugar Brown Light Golden Med 25Lb | 1 : 25 Lb | $ 23.32 |
VitCo | 01/03/2022 | Sugar Granulated 50 Lb. | 1 : 50 Lb | $ 31.57 |
VitCo | 01/03/2022 | Herbal Mint Fresh | 10 : 1 Lb | $ 73.20 |
VitCo | 01/03/2022 | Orange #1 | 1 : 15 Lb | $ 12.83 |
VitCo | 01/03/2022 | Milk Sweetened Condensed Black & White | 24 : 14 Oz | $ 47.32 |
VitCo | 01/03/2022 | Salt Sea Coarse 6.6 Lb | 6 : 6.6 Lb | $ 63.31 |
DC | 01/03/2022 | Milk Soy Vanilla Organic | 12 : 32 Oz | $ 17.11 |
DC | 01/03/2022 | Filter Coffee 24 x 11 Urn Paper | 1 : 250 Cs | $ 23.94 |
DC | 01/03/2022 | Holder Cup 4 8-32 Oz | 1 : 300 Ct | $ 40.07 |
DC | 01/03/2022 | Cup 12 Oz Clear PP 360Cc U-Rim (Employee) | 1 : 2000 Pcs | $ 67.24 |
DC | 01/03/2022 | Lid Flat Clear 12 Oz U-Rim Cup (Employee) | 1 : 2000 Ct | $ 38.32 |
DC | 01/03/2022 | Grass Jelly Liquid Form Can | 12 : 27 Oz Can | $ 62.83 |
Here's a snapshot of my dashboard:
The new columns will be added to the table below and they are highlighted in red.
I changed your file and uploaded it here: https://ufile.io/wyulzcg7
It should work now. Added a measure called 'prevprice', which was your issue.
Hope it all works now!
Thank you for fixing the file @Anonymous. As I was trying to tweak with the file, it seems the data is not correct. There should be values for the Price Change ($) and Price Change (%) for the specific date right?
For example:
Under the following values:
Price change for January 10 should be $32.18 - $23.71 = $8.47 so on and so forth.
Attaching the file again as I've missed it.
File: https://drive.google.com/file/d/135GJ9O4jhVUOuXFrgEwEmzVOEgFiQ8HS/view?usp=sharing
hi @7LeavesJustine ,
I tried to recreate what you want from your dummy data. Added another month to be able to compare numbers.
I created on calculated column to get the previous value of the product.
Previous Cost per Case =
VAR PreviousRow =
TOPN (
1,
FILTER (
Sheet1,
Sheet1[Delivery Date] < EARLIER ( Sheet1[Delivery Date] )
&& Sheet1[Product Description] = EARLIER ( Sheet1[Product Description] )
),
Sheet1[Delivery Date], DESC
)
VAR PreviousValue =
MINX ( PreviousRow, [ Cost Per Case ] )
RETURN
PreviousValue
Then you can create your measures by comparing the current price to this column.
Price Change ($) =
VAR current_price = CALCULATE([Cost Per Case], MAX(Ark1[Delivery Date]) = Sheet1[Delivery Date])
VAR prevprice = CALCULATE(SUM(Sheet1[Previous Cost per Case]), MAX(Sheet1[Delivery Date]) = Sheet1[Delivery Date])
Return
current_price - prevprice
Price Change (%) =
DIVIDE([Price Change ($)], [prevprice])
Change Label =
IF([Price Change ($)] > 0, "Increase", IF([Price Change ($)] < 0, "Decrease", "No Change"))
As for top 10, I reccomend checking out these articles, which explains this topic very well. Or simply use the filter on the filter pane for top 10 by price or what you want.
https://radacad.com/topn-dax-function-how-it-works-in-power-bi-comparison-against-the-top-group
Hope this helped you.
Please mark it as resolved if this is what you're looking for 🙂
Thank you for the quick response @Anonymous . I appreciate the time you've put to discuss the solution.
Am I doing it right for this formula?
I put "Sum" beofre the 'Sum Data'[Cost Per Case'] in the VAR current_price as it does not read a column and I'm not sure if I'm getting the right values.
Price Change ($) =
VAR current_price = CALCULATE(SUM('Sum Data'[Cost Per Case]), MAX('Sum Data'[Delivery Date]) = 'Sum Data'[Delivery Date])
VAR prevprice = CALCULATE(SUM('Sum Data'[Previous Cost per Case]), MAX('Sum Data'[Delivery Date]) = 'Sum Data'[Delivery Date])
Return
current_price - prevprice
Hi @7LeavesJustine ,
Sorry about that. I created a measure called
Thank you @Anonymous. I am able to Price Change ($) but there is an error with Price Change (%).
Error: The value for 'prevprice' cannot be determined. Either the column doesn't exist, or there is no current row for this column.
Price Change (%) =
DIVIDE([Price Change ($)], [prevprice])
I have also attached my test pbix file for us to seamlessly make changes. Here's the link:
https://drive.google.com/file/d/12nMhyBQGVChgC077T0htzTm2bpbzoZSG/view?usp=sharing
User | Count |
---|---|
85 | |
80 | |
64 | |
53 | |
46 |
User | Count |
---|---|
101 | |
50 | |
41 | |
39 | |
38 |