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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Creating calculated table based on slicer selected values [or different approach]

Hi Everyone, 

 

I'd like to come up with calculation for Final calculation. Where I'd have 3 columns: Upsell, Downsell and revenue Diff

First table is datasource. 

Then I've slicer with selected time period. 
The calculation should be on organization_id level: Revenue(end_period) - Revenue(beginning_period)

If calculation is positive --> Upsell, If negative --> Downsell

 

The issue which I have is that I have to keep calculation on Organization_id level in order to have clear view on Upsell and Downsell (have to keep it separate).

 

I tried couple of approachech, but nothing seems to be working. 

 

I tried to create calculated table, but unfortunatelly it's not dynamic (based on slicer selected values) ... in other words it's being created only once. 

 

calculation.PNG

 

I'd be really happy for any suggestions. 

 

Thank you

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi  @Anonymous 

First, you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result in a visual.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

 

Second, for your case, you could try this way to create three table visual to get it:

Revenue_diff = 
var _minYM=CALCULATE(MIN('Table'[Year_month]))
var _maxYM=CALCULATE(MAX('Table'[Year_month]))
return
CALCULATE(SUM('Table'[Revenue]),FILTER('Table','Table'[Year_month]=_maxYM))-CALCULATE(SUM('Table'[Revenue]),FILTER('Table','Table'[Year_month]=_minYM))
Upsell = 
var _table=SUMMARIZE('Table','Table'[Organization_id],"_diff",[Revenue_diff])
return
SUMX(FILTER(_table,[_diff]>0),[Revenue_diff])
Downsell = 
var _table=SUMMARIZE('Table','Table'[Organization_id],"_diff",[Revenue_diff])
return
SUMX(FILTER(_table,[_diff]<0),[Revenue_diff])

 

Result:

1.JPG

here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

hi  @Anonymous 

First, you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result in a visual.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

 

Second, for your case, you could try this way to create three table visual to get it:

Revenue_diff = 
var _minYM=CALCULATE(MIN('Table'[Year_month]))
var _maxYM=CALCULATE(MAX('Table'[Year_month]))
return
CALCULATE(SUM('Table'[Revenue]),FILTER('Table','Table'[Year_month]=_maxYM))-CALCULATE(SUM('Table'[Revenue]),FILTER('Table','Table'[Year_month]=_minYM))
Upsell = 
var _table=SUMMARIZE('Table','Table'[Organization_id],"_diff",[Revenue_diff])
return
SUMX(FILTER(_table,[_diff]>0),[Revenue_diff])
Downsell = 
var _table=SUMMARIZE('Table','Table'[Organization_id],"_diff",[Revenue_diff])
return
SUMX(FILTER(_table,[_diff]<0),[Revenue_diff])

 

Result:

1.JPG

here is sample pbix file, please try it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-lili6-msft  That's perfect ... the only problem is that when there's no value for min_month, the calculation is not calculated correctly. 

For example if there is an organization, which starts at 2020-02 and slicer is selected from 2020-01 it'll calculate the final value as Donwsell.

amitchandak
Super User
Super User

@Anonymous , You can not create a dynamic table using slicer.

 

You can create dymanic table in measure calculation. Not outside it

Like

countx(filter(summarize(Table,Table[customer],"_1", distinctCOUNT(Table[contact Id])),[_1]>=2),[customer])

or

measure =

Var _tab =summarize(Table,Table[customer],"_1", distinctCOUNT(Table[contact Id]))

countx(filter(_tab,[_1]>=2),[customer])

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

I tried to do it as you said ... create a measure (variables), but I'm not sure how to combine there 2 variables (min_table and max_table) in order to get 1 result --> where I'd be able to compare min_revenue(min_arr) and max_revenue(max_arr).

VAR max_month = CALCULATE(MAX(DATES_QBR[YEAR_MONTH]),ALLSELECTED(DATES_QBR))
VAR max_table = FILTER(
SUMMARIZE(NEW_ARR_TABLE
                ,NEW_ARR_TABLE[ORGANIZATION_ID]
                ,NEW_ARR_TABLE[YEAR_MONTH]
                ,"Max_ARR", FIRSTNONBLANKVALUE(NEW_ARR_TABLE[ARR],0))
                ,NEW_ARR_TABLE[YEAR_MONTH]=max_month
)

VAR min_month = CALCULATE(MIN(DATES_QBR[YEAR_MONTH]),ALLSELECTED(DATES_QBR))
VAR min_table = FILTER(
SUMMARIZE(NEW_ARR_TABLE
                ,NEW_ARR_TABLE[ORGANIZATION_ID]
                ,NEW_ARR_TABLE[YEAR_MONTH]
                ,"Min_ARR", FIRSTNONBLANKVALUE(NEW_ARR_TABLE[ARR],0))
                ,NEW_ARR_TABLE[YEAR_MONTH]=min_month
)

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Top Solution Authors