Forum Discussion
Helper IDivide 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?
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
cferv_77UPDATE:
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?
- cferv_77
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.