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.
I'm trying to track any/all price changes over a period of time across multiple suppliers. In doing so, I pivoted the data with the Date in the columns, SKU in the rows, and Cost within the values. That works perfectly for my needs, but I also need a flag that indicates if there's a change in price per SKU/row. I tried doing a MAX minus MIN for each line, which worked, but that wouldn't factor Supplier so it would compare all.
Here's a sample of the unpivoted data, with the highlighted portion being what I need to trigger a flag comparing day-over-day changes at both the SKU and Supplier level:
What's the best way of determining a "PriceChangeFlag" by each supplier and sku?
Solved! Go to Solution.
HI @kahnailee,
You can try to use following measure to return tag based on diff between current and previous cost:
Diff Tag= VAR currDate = MAX ( Table3[Date] ) VAR currSKU = SELECTEDVALUE ( Table3[Sku] ) VAR currSupplier = SELECTEDVALUE ( Table3[Supplier] ) VAR prevDate = CALCULATE ( MAX ( Table3[Date] ), FILTER ( ALLSELECTED ( Table3 ), [Date] < currDate && [Supplier] = currSupplier && [Sku] = currSKU ) ) VAR prevCost = CALCULATE ( MIN ( Table3[Cost] ), FILTER ( ALLSELECTED ( Table3 ), [Date] = prevDate && [Supplier] = currSupplier && [Sku] = currSKU ) ) VAR result = IF ( prevCost <> BLANK (), MAX ( Table3[Cost] ) - prevCost, 0 ) RETURN IF ( result > 0, "↑", IF ( result < 0, "↓", "-" ) )
In addition, you can also create a calculated column with dynamic hex color code based on diff, then use conditional formatting feature to add color to original field value.
Conditional formatting in tables
Regards,
Xiaoxin Sheng
I'm thinking something like:
Measure Flag = VAR __date = MAX([Data_Date]) //current date VAR __sku = MAX([SKU]) //current sku VAR __supplier = MAX([Supplier]) //current supplier VAR __prevCostDate = MAXX(FILTER(ALL('Table'),[SKU]=__sku && [Supplier]=__supplier && [Data_Date]<__date),[Data_Date]) VAR __prevCost = MAXX(FILTER(ALL('Table'),[SKU]=__sku && [Supplier]=__supplier && [Data_Date]=__prevCostDate),[Cost]) RETURN IF([Cost]<>__prevCost,1,0)
That would work if there was one sku, I believe, but I have thousands. Therefore, I get an error of "a single value for column 'Cost' cannot be determined". Any ideas?
Sorry, missed an aggregation.
Measure Flag = VAR __date = MAX([Data_Date]) //current date VAR __sku = MAX([SKU]) //current sku VAR __supplier = MAX([Supplier]) //current supplier VAR __prevCostDate = MAXX(FILTER(ALL('Table'),[SKU]=__sku && [Supplier]=__supplier && [Data_Date]<__date),[Data_Date]) VAR __prevCost = MAXX(FILTER(ALL('Table'),[SKU]=__sku && [Supplier]=__supplier && [Data_Date]=__prevCostDate),[Cost]) RETURN IF(MAX([Cost])<>__prevCost,1,0)
That works, but it doesn't factor each supplier. Meaning, it will look across all suppliers to see if there's a change overall, not looking at one supplier for the available dates. We're getting close though.
That depends on the context of your visual. If you have a visual setup the way you do in your picture, then it should work. That is why I do a MAX([Supplier]) and then use that in the filters do make sure I am dealing with the correct supplier.
This is ideally how I'd like to see the data day over day. I'd also like to have the ability to filter out only those in which have changes (outside of those with zero's or blanks).
I've also put in a filter for Supplier so that should help reduce the anomalies, but I still need the filterable flag.
HI @kahnailee,
You can try to use following measure to return tag based on diff between current and previous cost:
Diff Tag= VAR currDate = MAX ( Table3[Date] ) VAR currSKU = SELECTEDVALUE ( Table3[Sku] ) VAR currSupplier = SELECTEDVALUE ( Table3[Supplier] ) VAR prevDate = CALCULATE ( MAX ( Table3[Date] ), FILTER ( ALLSELECTED ( Table3 ), [Date] < currDate && [Supplier] = currSupplier && [Sku] = currSKU ) ) VAR prevCost = CALCULATE ( MIN ( Table3[Cost] ), FILTER ( ALLSELECTED ( Table3 ), [Date] = prevDate && [Supplier] = currSupplier && [Sku] = currSKU ) ) VAR result = IF ( prevCost <> BLANK (), MAX ( Table3[Cost] ) - prevCost, 0 ) RETURN IF ( result > 0, "↑", IF ( result < 0, "↓", "-" ) )
In addition, you can also create a calculated column with dynamic hex color code based on diff, then use conditional formatting feature to add color to original field value.
Conditional formatting in tables
Regards,
Xiaoxin Sheng
@Xiaoxin Sheng
Wow, that's an interesting solution. Do you happen to have the file you put together from the screenshot? If so, do you mind sharing?
HI @kahnailee,
I Just add measure to value field and enable conditional formatting based on calculated column. I attached sample file below.
Regards,
Xiaoxin Sheng
Hi,
So will you apply a filter on any one supplier and then see the result? If you have 500 suppliers, you will have to filter 500 times. Am i missing something?
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 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |