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

Helper I

## Divide Measure not working as intended

Hello,

I have this data model (see image below) that involves 3 tables ( one table related to location, two tables related to items)

I have a few measures that uses the item-related tables. I noticed some measures work fine when I incorporate them with the City field (from the Location table). However, other measures are not working as expected. For example...

I created a table visual using the City (from Location table) and few measures. The measures, number of deliveries and the deliveries per item count, work fine but for some reason, the delivery/ALL(deliveries) is not working. It keeps giving 100% across all the city when I want the pct of that city based on all deliveries. So there are 100 deliveries in NYC and there has been a total of 1000 deliveries for the year/period, it should show 10%, not 100%.

Location table has a 1-to-Many relationship with Table1 and Table2.

The DAX formula I am using is:

Delivery Pct of Deliveries = DIVIDE[Delivery Count]CALCULATE[Delivery Count], ALL(Table2[City] ) ), BLANK() )

Any ideas?

1 ACCEPTED SOLUTION
Helper I

Solved this problem by removing [Delivery Pct of Deliveries] measure and instead focusing on using the [# of deliveries] measure as an alternative. By selecting show the value as 'Percent of grand total', I got the results I needed while display all the information correctly.

2 REPLIES 2
Helper I

Solved this problem by removing [Delivery Pct of Deliveries] measure and instead focusing on using the [# of deliveries] measure as an alternative. By selecting show the value as 'Percent of grand total', I got the results I needed while display all the information correctly.

Helper I

UPDATE:

The measure works when I reference the City column from the Location table instead of the Table2, replacing it with ALL(Location[City] ). However, I was originally using a bar visual to drill down to county level. I do have a separate County table that also has a relationship with Table2.

Is there a way to have this information displayed correctly when drilled up/down in one measure instead of two? Like using SELECTEDVALUE with Fields parameters?

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.

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

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

Top Solution Authors
Top Kudoed Authors