Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
So I have the following simple measure.
Msr Fg = CALCULATE(
COUNTROWS(FctTable),
FctTable[TypeOfIncident] = "xxx"
)
This measure is visualised in a Column Chart, with on the X-axis the month-year from the Datetable.
This works fine.
Now, how can I divide these Monthly values in the Chart by a value from another table depending on the monthyear?
I've created a relationship between Monthyear from datetable and monthyear from this AB-table.
MonthYear A B
Nov-21 | 20000 | 23000 |
Dec-21 | 21000 | 24000 |
Jan-22 | 22000 | 25000 |
Feb-22 | 20000 | 23000 |
Mar-22 | 21000 | 24000 |
Apr-22 | 22000 | 25000 |
May-22 | 19000 | 18000 |
Help is much appreciated 😄
Solved! Go to Solution.
Yes, I would expect it to be filtered by putting Month Year in the X axis.
You could try
Msr Fg2 =
var AValue = LOOKUPVALUE( 'AB Table'[A], 'AB Table'[Month year], SELECTEDVALUE('Date'[Month year]))
return DIVIDE( [Msr Fg], AValue)
Works like a charm, Thank you!
As the date table is filtering the AB table you can use SELECTEDVALUE
New measure = DIVIDE( [Msr Fg], SELECTEDVALUE('AB Table'[A]))
This produces a blank Chart:
Msr Fg2 =
DIVIDE(
[Msr Fg],
SELECTEDVALUE('ABtable'[A])
)
SelectedValue: Returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult.
When I try this:
Msr Fg2 =
DIVIDE(
[Msr Fg],
SELECTEDVALUE('ABtable'[A],10)
)
Divides all values by 10. So not filtered to one distinct value I guess.
When you say: "As the date table is filtering the AB table you can use SELECTEDVALUE".
You mean it's filtered in the visual by putting Monthyear in the X-axis?
Yes, I would expect it to be filtered by putting Month Year in the X axis.
You could try
Msr Fg2 =
var AValue = LOOKUPVALUE( 'AB Table'[A], 'AB Table'[Month year], SELECTEDVALUE('Date'[Month year]))
return DIVIDE( [Msr Fg], AValue)
User | Count |
---|---|
143 | |
71 | |
69 | |
53 | |
52 |
User | Count |
---|---|
208 | |
94 | |
64 | |
60 | |
57 |