Forum Discussion
How to make a slicer modify a measure
Hello,
I am a beginner user of Power BI and I have a data table with trial balance data. Each expense account has 2 grouping name columns. The first grouping is the account description 1, i.e. Salaries & Wages, Repairs & Maintenance and the second grouping description is "operating expense" where they both roll up too... I want to create a measure to calculate each account group's percent of Income. For example Salaries&Wages/Operating Income, but I want to do this for all the account categories and be able to use a slicer to determine how the measure would return the value.
For example, if the calculation was using = Operating Expense/Operating Income, and the slicer is the description 1 value and Salaries & Wages is selected, then the measure would only look for the salaries & wages description 1 and divide the that by operating income. Also, is this possible to achieve this without creating a measure for each individual account type? Almost as if the slicer is acting like a VLookup to then tell the measure return a value that is displayed in the visual?
here is a simplified version of my data table:
| yearMonthNum | fomDate | cat07Descr1 | cat07Descr2 | actualAmt |
| 202001 | Jan-20 | Repairs and maintenance | Operating Expense | 1000 |
| 202001 | Jan-20 | Salaries & Wages | Operating Expense | 400 |
| 202001 | Jan-20 | Travel Expense | Operating Expense | 600 |
| 202001 | Jan-20 | Widget Income | Operating Income | -15000 |
17 Replies
- Ashish_Mathur
Super User
Hi,
Share some data and show the expected result.
Here is what a sample data table would look like:
Date Reporting Period Account Description 1 Account Description 2 Amount 013121 1 Repairs and Maintenance Operating Expense 1000 013121 1 Salaries & Wages Operating Expense 400 013121 1 Travel Expense Operating Expense 600 013121 1 Widget Income Operating Income -1600
My slicer would be Account Description 1
If Repairs & Maintenence is selected in slicer, the measure would return:
'Repairs & Maintenane' % of Income = 6.67%
If Travel Expense was selected in the slicer, the measure would return:
'Travel Expense' % of Income = 4%
Sorry, I don't know why the table keeps looking funny/weird:
Date
Reporting Period
Account Description 1
Account Description 2
Amount
013121
1
Repairs and Maintenance
Operating Expense
1000
013121
1
Salaries & Wages
Operating Expense
400
013121
1
Travel Expense
Operating Expense
600
013121
1
Widget Income
Operating Income
-1600
- v-angzheng-msft
Community Support
Hi, 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.
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
- v-angzheng-msft
Need to log in, can't access.
You'd better explain the function of your date column and show your expected results, otherwise I will not be able to help, just like shooting an arrow in the dark.I'm a little confused, your needs don't seem to have anything to do with the date. How did you get the different results? Do you have any other factors that need to be considered?
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?
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.I'm sorry about that - Seems like our company did not allow open links and requires providing access by invites via external emails.
I'll include some snapshots that I hope will be helpful.
I'm going to focus on CenterName A, January 2020: Expected result -4.48% Salary Exp/Operating Income for that month, for that property
Results:
I created a Salary Expense Measure (which is not dynamic) - used it in the visual on the top-right quadrant.
The two visuals in the bottom are using the suggested dynamic measure.
Snip of Visualizations/Fields pane on table visual (bottom left)
Snip of Visualizations/Fields pane on Matrix visual (bottom right)
Snip of Tables: (filtered for values shown)
CostCenterData
FinancialData table
Reporting date table:
_Calc Result Measure
_Calc Result =IF(ISFILTERED('FinancialData'[cat07Descr1]),SUMX(ADDCOLUMNS('financialData',"_Calc Result",[_Calc]),[_Calc Result]),1)_Calc Measure_Calc =var _subTable=FILTER (ALL( 'FinancialData'),'FinancialData'[CenterName]=MAX('FinancialData'[CenterName])&&'FinancialData'[fomDate]=MAX('FinancialData'[fomDate]))VAR _Income =MAXX(FILTER (_subTable,'FinancialData'[cat07Descr2] = "Operating Income"),'FinancialData'[naturalSignActualAmt])VAR _a =CALCULATE (SUM ( FinancialData[naturalSignActualAmt] ),ALLSELECTED ( 'FinancialData'[cat07Descr1] ))VAR _divide =DIVIDE ( _a, _Income )VAR _total =IF ( ISFILTERED ( 'FinancialData'[cat07Descr1] ), _divide, 1 )RETURN_totalCalc_SalExptoRev% Measure (the one I used on the top right visual)
Calc_SalExptoRev% =Calculate(DIVIDE(CALCULATE(SUM('FinancialData'[naturalSignActualAmt]), 'FinancialData'[cat07Descr1]="Salary expense"),(CALCULATE(SUM('FinancialData'[naturalSignActualAmt]), 'FinancialData'[cat07Descr2]="Operating Income")),0))Relationships of TablesThank you again for your help!
