Forum Discussion
Helper IICorrect DAX and Measure for % completed by State?
Hi,
I do not have my measure or DAX (or both) setup correct. I searched and could not find any solutions. Can you review and provide guidance on this?
Data Info.
Table 1 = Total Population of products that need to be "completed". Each has a unique ESN and has assign state
Table 2 = Products that have been "completed". Shown by ESN and date "completed.
When I tried the DAX and measure to show Percentage completed by State it is not working. It is dividing the total count of completed divided by Total Quantity by State. My Completed table does not have state data, it is related by ESN to total population table which has state.
The correct calculation should be Total number of completed in the specific State Divided by Total number of Population in that specific state. Example = If California only has Qty 10 from the Total population. And 2 products have been completed (Table 2) from Calfornia, then the percentage Completd for California = 20%.
Right now it is calculating total completed divided by total in the state.
Before anything else, try making the directon between your two tables completed and population Both instead of one direction.
5 Replies
- Greg_Deckler
Community Champion
Before anything else, try making the directon between your two tables completed and population Both instead of one direction.
efowlerGot it thank you! Change the relationship cross filter to BOTH and rewrote the measure and it worked perfect.. Thank you!
Now I need to do some research on what exactly that does so I can understand it for the future..
- edhans
Can you provide some data, and what your measures are? For example one of your variables uses [count per state] but i don't know what that measure is, and I suspect that is part of the problem.
And you said Table1 and Table2 had some data, but your model doesn't show a Table1 or Table2.
If you could share your PBIX via OneDrive or DropBox that would be immensly helpful. Otherwise we will need to spend time typing in data just to get to where you are.
- efowler
Hi, thank you for looking at the post.. The link below will allow you to download the data file and the .pbix file.
I mention Table 1 and Table 2 but they are named differently in the model (sorry).
Completed1 = Data showing what has been Completed (related by ESN)
Population = Data showing total population (related by ESN)
https://app.box.com/s/w5x5utwdscw4efm6uxfkc5ewn2b0c3ut
- Greg_Deckler
Took a crack at this. No idea if the numbers are correct. PBIX is attached. Basically, changed the relationship as suggested earlier. Messed with the % Complete calc.
