Hi All,
Please need your help on total mismatch issue by using only DAX code. The order filter reasons should sum up with total orders but it doesn’t. Tried my best but couldn’t find the answer. I have attached the power bi file and description in the file. Appreciated for you prompt reply. Thank you.
Regards,
PratikDAX Total Amount Mismatch While Filtering.pbix (37.0 KB)
Hi @Pratik,
If I understand your scenario correctly, it looks like you are trying to remove duplicates using the Key_ID column, so only the first Order Reason will remain with their corresponding Orders value.
Pay attention that DAX is not preserving the row order so you cannot base your formulas on the order you see into the table. Just for a quick test - create a table visual and put all of the three columns on it and you’ll see that for Key_ID=1 you have different display order in the data table vs. visual table.
In data table you’ll have:
Urgent
Can wait for the future
In visual table you’ll have:
Can wait for the future
Urgent
Because of that, at least part of the work should be done in Power Query.
The first option is to have all done in Power Query - either referencing your original table if needed for another reasons, then remove duplicates after you select the Key_ID column. The resulting table could then be used without any other complicated formulas. (see the attached file with table Data NoDuplicates)
A second option would be partially done in Power Query where you must add an Index column.
Then in DAX you could create a NewValue column that will return the Order qty only for the first occurence of the Key_ID column:
NewValue = IF('Data Indexed'[Index]=MINX(FILTER('Data Indexed','Data Indexed'[Key_ID]=EARLIER('Data Indexed'[Key_ID])),[Index]),[Orders],0)
And the complete file: DAX Total Amount Mismatch While Filtering_L1.pbix (52.3 KB)
Kind Regards,
Lucian
Thank you Lucian , very much helpful. Appreciated a lot.
Regards,
Pratik