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.
Hi,
I want to calculate a measure where I sum up the growth of all products that have growth vs. last year and in another measure, I want to aggregate the decline of all the products that have a decline.
I've tried with
Growth=If([This Year] > [Last year]; [This Year]-[Last Year])
Decline=If[This Year]<[Last year];[This Year]-[Last Year])
It works on an aggregated level but it sums up the whole year and not per product. I would like to see both how much that is growth and how much that is decline...
Hope you can help
Solved! Go to Solution.
Hi @Anonymous,
You can create a calculated column like below to get values in last year same weeknr for the same product:
LastYearSameSKU-WEEKNR = LOOKUPVALUE(data[Value Sales'000],data[PRODUCT],'data'[PRODUCT],data[WEEKNR],'data'[WEEKNR],data[YEAR],'data'[YEAR]-1)
Best Regards,
QiuyunYu
Add Product to your visual?
Thanks, @Greg_Deckler, I wished it was that simple - The visualization I use is a Stacked Column where I have growth, decline and value last year in three different column values and then Value this year in a Line, The 3 columns add up to this year's value.
When I have more than one value in Column values I cannot have anything in Column series...
On the chart above I should have both growths and decline in each period - Now I only have the net growth or decline.
In Excel I can fix it, so it should look something like this:
Hi @Anonymous,
It's expected that we can't add column to Column Series property if we already add more than one fields into Column Values section.
In your scenario, please share some sample data and show us the desired results, so we can test to see if your requirement can be achieved.
Best Regards,
QiuyunYu
Thanks for coming back @v-qiuyu-msft.
You can find some data here:
https://www.dropbox.com/sh/vtld3ulkzqmi1ym/AAAxsCvX1DDnCa20e4WVsZ-0a?dl=0
I have been thinking - if I can add a column in the table that looks up last years sales on each SKU, Year, Period it should be easy. There is only one row per SKU/Period....
Thanks for looking into this!
Kent
Hi @Anonymous,
You can create a calculated column like below to get values in last year same weeknr for the same product:
LastYearSameSKU-WEEKNR = LOOKUPVALUE(data[Value Sales'000],data[PRODUCT],'data'[PRODUCT],data[WEEKNR],'data'[WEEKNR],data[YEAR],'data'[YEAR]-1)
Best Regards,
QiuyunYu
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 |
---|---|
111 | |
96 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |