cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Anonymous
Not applicable

## Dividing monthly value in chart by a value from another table

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 😄

1 ACCEPTED SOLUTION
Super User

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)``````
4 REPLIES 4
Anonymous
Not applicable

Works like a charm, Thank you!

Super User

As the date table is filtering the AB table you can use SELECTEDVALUE

``New measure = DIVIDE( [Msr Fg], SELECTEDVALUE('AB Table'[A]))``
Anonymous
Not applicable

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?

Super User

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)``````

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors