Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Julianaz
Helper II
Helper II

How to add a column with some row values according to time intelligence aggregation?

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  
ItemclassProfit
GoldenAppleFruitLoss
RoseAppleFruitLoss
GreenAppleFruitLoss
RedAppleFruitLoss
GreenPepperVeggieGain
BellPepperVeggieGain
BalanaPepperVeggieGain
 

 

 

Table2  
Dateitemsales
1-JanGoldenApple30
2-JanRoseApple-50
1-MayGreenApple60
1-JunRedApple-70
1-JanGreenPepper30
1-MarBellPepper40
1-JulBalanaPepper-90
….
5 REPLIES 5
newhopepdx
Helper I
Helper I

 

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? 

 

Julianaz_0-1693505774972.png

 

Ouch! As you see, I'm still learning the amazing (but complicated) tool as well.

newhopepdx
Helper I
Helper I

Create a measure:
Total Sales =
SUM(Sales[Sales])
 
Use that to add a column to the Fruit table:
Gain or Loss =
IF(
    [Total Sales] > 0,
    "Gain",
    "Loss"
    )

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.

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.