Forum Discussion
Conditional measure
Hi Guys,
I have come across a rather interesting situation. I have a table in which I have an ACCOUNT column. It is mapped with the other table (name_entity column).
In the matrix in the rows I have values from the name_entity column. I need to write a measure that conditionally, for all rows will take some there specified value, and for one particular one, say “% of Cash Invested” will take dividing values from two other rows “Total Average Cash” and “Cash Invested” which are counted by this measure.
As you can see, it should come out 73%. How to do this?
2 Replies
Hi,
The issue is that for the % of Cash Invested row, SELECTEDVALUE() only gives you the current matrix row (% of Cash Invested). It doesn't automatically retrieve the values from the other two matrix rows.
You can solve this by explicitly calculating the two categories inside the measure and then dividing them.
Try this:
AVG_CASH_PERCENT_BUD =
VAR CurrentCategory =
SELECTEDVALUE ( 'AVG_CASH_ACT'[CATEGORY] )
VAR TotalAverageCash =
CALCULATE (
[AVG_CASH_BUD],
REMOVEFILTERS ( 'AVG_CASH_ACT'[CATEGORY] ),
'AVG_CASH_ACT'[CATEGORY] = "Total Average Cash"
)
VAR CashInvested =
CALCULATE (
[AVG_CASH_BUD],
REMOVEFILTERS ( 'AVG_CASH_ACT'[CATEGORY] ),
'AVG_CASH_ACT'[CATEGORY] = "Cash Invested"
)
RETURN
IF (
CurrentCategory = "% of Cash Invested",
DIVIDE ( CashInvested, TotalAverageCash ),
[AVG_CASH_BUD]
)
Why this works
For normal rows, the measure simply returns:
[AVG_CASH_BUD]
But when the matrix is evaluating:
% of Cash Invested
it calculates:
Cash Invested
------------------
Total Average Cash
So, for example:
Cash Invested = 73
Total Average Cash = 100
73 / 100 = 73%
One important point
I would not use FORMAT() inside the measure:
FORMAT([AVG_CASH_BUD], "0%;(0%);-")
because FORMAT() converts the result to text, which can cause problems with totals, sorting, conditional formatting, and further calculations.
Instead, keep the measure numeric:
DIVIDE ( CashInvested, TotalAverageCash )
and format the measure as a percentage from Modeling → Format → Percentage.
If your % of Cash Invested row needs to use the YTD/month calculation from [AVG_CASH_BUD] exactly as your existing measure does, the above pattern will preserve that logic because it calls [AVG_CASH_BUD] for both categories.
Hope this helps.
As always, please a workable sample data and your expected result from that. It is hard to figure out what you want to achieve from the description alone. https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
