Forum Discussion

shelleye's avatar
shelleye
Frequent Visitor
5 years ago

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....
Need Help
v-angzheng-msft's avatar
v-angzheng-msft
Icon for Community Support rankCommunity Support
5 years ago

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.

MarthaC's avatar
MarthaC
Frequent Visitor
5 years ago

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.

 

 

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

 

Thank you again for your help!