Skip to main content
cancel
Showing results for 
Search instead 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

Reply
cferv_77
Helper I
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() )

 

 

Example.png

 

Any ideas? 

1 ACCEPTED SOLUTION
cferv_77
Helper I
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.

View solution in original post

2 REPLIES 2
cferv_77
Helper I
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.

cferv_77
Helper I
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? 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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