cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors