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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Anonymous
Not applicable

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 III
Helper III
Anonymous
Not applicable

 

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 III
Helper III

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"
    )
Anonymous
Not applicable

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
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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

Top Solution Authors