Forum Discussion
How to make a slicer modify a measure
Community SupportHi, shelleye
Try to create a measure like this:
Measure =
var _Income=MAXX(FILTER(ALL('Table'),'Table'[Account Description 2]="Operating Income"),ABS('Table'[Amount]))
var _a=CALCULATE(ABS(SUM('Table'[Amount])),ALLSELECTED('Table'[Account Description 1]))
var _divide=DIVIDE(_a,_Income)
var _total=IF(ISFILTERED('Table'[Account Description 1]),_divide,1)
return _totalFormat the measure as Percentage
Result:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the measure.
What if the data table has revenue centers and multiple reporting periods and the request is to calculate the % of the Account description 1 by Operating Income for each individual period and revenue center?
Example of what the table would look like:
Date Reporting Period CenterName Account Description 1 Account Description 2 Amount Calc Result 13121 1 A Repairs and Maintenance Operating Expense 1000 -62.50% 13121 1 A Salaries & Wages Operating Expense 400 -25.00% 13121 1 A Travel Expense Operating Expense 600 -37.50% 13121 1 A Widget Income Operating Income -1600 100.00% 22821 2 A Repairs and Maintenance Operating Expense 1100 -55.00% 22821 2 A Salaries & Wages Operating Expense 440 -22.00% 22821 2 A Travel Expense Operating Expense 650 -32.50% 22821 2 A Widget Income Operating Income -2000 100.00% 13121 1 B Repairs and Maintenance Operating Expense 900 -41.86% 13121 1 B Salaries & Wages Operating Expense 425 -19.77% 13121 1 B Travel Expense Operating Expense 525 -24.42% 13121 1 B Widget Income Operating Income -2150 100.00% 22821 2 B Repairs and Maintenance Operating Expense 850 -37.78% 22821 2 B Salaries & Wages Operating Expense 450 -20.00% 22821 2 B Travel Expense Operating Expense 650 -28.89% 22821 2 B Widget Income Operating Income -2250 100.00% thank you
- v-angzheng-msft
Hi, MarthaC
a measure like this:
_Calc = var _subTable= FILTER ( ALL( 'T2'),'T2'[CenterName]=MAX('T2'[CenterName])&&'T2'[Reporting Period]=MAX('T2'[Reporting Period])) VAR _Income = MAXX( FILTER ( _subTable, 'T2'[Account Description 2] = "Operating Income" ), 'T2'[Amount] ) VAR _a = CALCULATE ( SUM ( 'T2'[Amount] ), ALLSELECTED ( 'T2'[Account Description 1] ) ) VAR _divide = DIVIDE ( _a, _Income ) VAR _total = IF ( ISFILTERED ( 'T2'[Account Description 1] ), _divide, 1 ) RETURN _totalCreate another measure to get the correct total.
_Calc Result = IF ( ISFILTERED ( T2[Account Description 1] ), SUMX ( ADDCOLUMNS ( 'T2', "_Calc Result", [_Calc] ), [_Calc Result] ), 1 )result:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.First, thank you so much for all the help you are providing us! We are so appreciative of it.
We applied these measures in our model, but our reporting periods are dates so it's returning funky results. They are not assigned 1,2 as in your pbix file. Could you help modify the measure so it works with and first of month date? i.e. 012021, 022021, 032021 date format?
