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

[Anonymous]

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. 

I'd be really happy for any suggestions. 

Thank you

[v-lili6-msft · Accepted solution]

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:

here is sample pbix file, please try it.

Regards,

Lin

[Anonymous]

@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]

@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])

[Anonymous]

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
)