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 folks, I am trying to determine the effect a single item has on the overall totals if I excluded it from last years total and this years total and divided those figures into each other.
I have 3 tables
The first is a returns table
Date | Return ID | Product Name |
01-Oct-2019 | 12358 | Apple |
05-Dec-2019 | 26959 | Grape |
28-Jan-2020 | 32659 | Banana |
09-Jun-2020 | 65323 | Grape |
The second is a Sales table
Date | Product Name | Sale Qty |
01-Sep-2019 | Apple | 1 |
10-Oct-2019 | Grape | 1 |
10-Mar-2020 | Apple | 3 |
27-May-2020 | Grape | 4 |
06-Jul-2020 | Banana | 2 |
The last is a Calendar table
Returns measure is -
Product Name | Total Returns | Total Sale | Return Rate | Return Rate YTD | Return Rate LY | Adjusted Return Rate YTD | Adjusted Return Rate YTD LY | Adjusted Ratio |
Apple | X | X | X | X | X | ? | ? | ? |
Grape | x | x | x | x | x | ? | ? | ? |
Banana | x | x | x | x | x | ? | ? | ? |
Hi @amitchandak I was looking over your suggestion again and I played with it a bit to try get it to work but I'm still having no luck getting it to return a value
This was the only attempt of mine that didn't throw up errors but still doesn't return any values
Really hoping someone can figure this one out as, I'm sad to say, this has me beat
@DaveCor , Try like
divide(
calculate([Total Returns],datesytd('Calendar'[Date]), all(returns_table)) - calculate([Total Returns],datesytd('Calendar'[Date])),
calculate([Total Sales ],datesytd('Calendar'[Date]), all(Sales_table)) - calculate([Total Sales ],datesytd('Calendar'[Date]))
)
Hi @amitchandak , thank you, I appreciate you taking the time to look at this.
I tried your suggestion but I am getting no values when I add it to my table, just a blank column.
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 |
---|---|
44 | |
28 | |
21 | |
12 | |
8 |
User | Count |
---|---|
74 | |
51 | |
45 | |
16 | |
12 |