Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
In Power BI(not power query), I would like to add a new column, say"Profit", some of the row value will be dynamic based on Class's column's YTD total sales. if total sales is positive, it will be value "Gain", if YTD total sales is negative, it will be value "Loss".
The row value will be dynamic according to what YTD has been selected in a matrix, eg. YTD to month end Jan could be positive for "Fruit", so should be "Gain"; but YTD to month end Feb could be negative for Fruit, so should be "Loss".
How to achieve this?
Thanks,
Table1 | ||
Item | class | Profit |
GoldenApple | Fruit | Loss |
RoseApple | Fruit | Loss |
GreenApple | Fruit | Loss |
RedApple | Fruit | Loss |
GreenPepper | Veggie | Gain |
BellPepper | Veggie | Gain |
BalanaPepper | Veggie | Gain |
… | … |
Table2 | ||
Date | item | sales |
1-Jan | GoldenApple | 30 |
2-Jan | RoseApple | -50 |
1-May | GreenApple | 60 |
1-Jun | RedApple | -70 |
1-Jan | GreenPepper | 30 |
1-Mar | BellPepper | 40 |
1-Jul | BalanaPepper | -90 |
… | …. | … |
Here's a link to dropbox with a sample .pbix and the .xlsx files. Is this what you're trying to do?
https://www.dropbox.com/scl/fi/h2bq4d3m7zn0xb6s5xs0q/Book1.xlsx?rlkey=h8bn6h8q0b9uu9q9eecc1t1o3&dl=0
Thanks for taking time to show me the sample. I appreciate that.
My real case senario will be a bit complicated: like I use group hierarchy and YTD time intelligency and it's just for some category not for all the category.
But again I believe the basic designning concept is the same. I wish to find some time to give your detailed sample data.
I also found from your visual: the sales >0 but the condition colunm is loss?
Ouch! As you see, I'm still learning the amazing (but complicated) tool as well.
Thanks for the quick reply.
I have the same basic concept of the solution.
I used YTD Totalling to replace sum;
But I didn't see the dynamic change of the value along with the time change.
User | Count |
---|---|
102 | |
90 | |
80 | |
71 | |
70 |
User | Count |
---|---|
114 | |
100 | |
97 | |
72 | |
71 |