Forum Discussion
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.
I'd be really happy for any suggestions.
Thank you
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
4 Replies
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
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.
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])
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 )
