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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
shelleye
Frequent Visitor

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:

 

yearMonthNumfomDatecat07Descr1cat07Descr2actualAmt
202001Jan-20Repairs and maintenanceOperating Expense1000
202001Jan-20Salaries & WagesOperating Expense400
202001Jan-20Travel ExpenseOperating Expense600
202001Jan-20Widget IncomeOperating Income-15000

 

17 REPLIES 17
v-angzheng-msft
Community Support
Community Support

Hi, @shelleye @MarthaC 

 

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.

@v-angzheng-msft 

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.

 

MarthaC_11-1631280573852.png

 

Snip of Visualizations/Fields pane on table visual (bottom left)

 

MarthaC_2-1631278485483.png

 

Snip of Visualizations/Fields pane on Matrix visual (bottom right)

MarthaC_3-1631278604103.png

 

Snip of Tables: (filtered for values shown)

CostCenterData

MarthaC_4-1631278682856.png

FinancialData table

MarthaC_5-1631278967624.png

Reporting date table:

MarthaC_8-1631280157133.png

 

_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
_total

Calc_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 Tables
MarthaC_12-1631281054255.png

 

Thank you again for your help!

 

MarthaC
Frequent Visitor

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:

 

        
DateReporting PeriodCenterNameAccount Description 1Account Description 2Amount Calc Result
131211ARepairs and MaintenanceOperating Expense1000 -62.50%
131211ASalaries & WagesOperating Expense400 -25.00%
131211ATravel ExpenseOperating Expense600 -37.50%
131211AWidget IncomeOperating Income-1600 100.00%
228212ARepairs and MaintenanceOperating Expense1100 -55.00%
228212ASalaries & WagesOperating Expense440 -22.00%
228212ATravel ExpenseOperating Expense650 -32.50%
228212AWidget IncomeOperating Income-2000 100.00%
131211BRepairs and MaintenanceOperating Expense900 -41.86%
131211BSalaries & WagesOperating Expense425 -19.77%
131211BTravel ExpenseOperating Expense525 -24.42%
131211BWidget IncomeOperating Income-2150 100.00%
228212BRepairs and MaintenanceOperating Expense850 -37.78%
228212BSalaries & WagesOperating Expense450 -20.00%
228212BTravel ExpenseOperating Expense650 -28.89%
228212BWidget IncomeOperating Income-2250 100.00%

 

 

thank you

v-angzheng-msft
Community Support
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 _total

 Format the measure as Percentage

vangzhengmsft_0-1630305947008.png

Result:

vangzhengmsft_1-1630306015999.png

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:

 

        
DateReporting PeriodCenterNameAccount Description 1Account Description 2Amount Calc Result
131211ARepairs and MaintenanceOperating Expense1000 -62.50%
131211ASalaries & WagesOperating Expense400 -25.00%
131211ATravel ExpenseOperating Expense600 -37.50%
131211AWidget IncomeOperating Income-1600 100.00%
228212ARepairs and MaintenanceOperating Expense1100 -55.00%
228212ASalaries & WagesOperating Expense440 -22.00%
228212ATravel ExpenseOperating Expense650 -32.50%
228212AWidget IncomeOperating Income-2000 100.00%
131211BRepairs and MaintenanceOperating Expense900 -41.86%
131211BSalaries & WagesOperating Expense425 -19.77%
131211BTravel ExpenseOperating Expense525 -24.42%
131211BWidget IncomeOperating Income-2150 100.00%
228212BRepairs and MaintenanceOperating Expense850 -37.78%
228212BSalaries & WagesOperating Expense450 -20.00%
228212BTravel ExpenseOperating Expense650 -28.89%
228212BWidget IncomeOperating Income-2250 100.00%

 

 

thank you

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
    _total

Create 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:

vangzhengmsft_0-1630461705039.png

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?

Hi, @shelleye 

 

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?   

Please consdier sharing more details about it or a simple sample file without any sesentive information for further discussion.

 

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 @v-angzheng-msft 

I have a sample .pbix file that I hope will be helpful.  On this file I created a visual using a measure that only looks at Salary Expense and another visual using the dynamic measure you provided to compare results.  

 

I've included the link to the onedrive.  

 

Sample test file 

 

 

Thank you again for your help,

best regards,

 

I don't know how to upload our sample pbix file with my options available in this reply.  

Ashish_Mathur
Super User
Super User

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Here is what a sample data table would look like:

 

 

DateReporting PeriodAccount Description 1Account Description 2Amount
0131211Repairs and MaintenanceOperating Expense1000
0131211Salaries & WagesOperating Expense400
0131211Travel ExpenseOperating Expense600
0131211Widget IncomeOperating 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%

How did you arrive at the 4% and 6.67%?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

MY APOLOGIES I was calculating on my real data:

 

It should be 1000/1600 = 62.5%  and 600/1600 = 37.5%,  respectively.

 

 

 

 

 

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

Hi,

Try these measures:

Total = sum(Data[actualAmt])

Widget total = calculate([total],data[Account Description]="Widget income")

Ratio = divide([total],[widget total])

Format the ratio measure as a %.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.