Forum Discussion
CALCULATE & ALLSELECTED Function not working across tables
Hi there,
I'm trying to create % to total calcuations between multiple tables. I'm using a Sales table and a Product Lookup table.
When I use the UPC from the sales table the Calculate AllSelected formula works fine to provide a total sales measure.
However, when I try to do the same thing using category or style attributes from the Product Lookup table, Power Bi only provides me with the total per whichever attribute I'm using.
As you can see the All Sales measure which = CALCULATE([Total $ Sales],ALLSELECTED('Aggregated Sales WTD & OH')), provides different answers. The one using the UPC from the sales table provides me with the correct answer, but the one form the lookup table does not.
Note: For some reason, I am getting a many to many relationship between the lookup and sales table; however, I've checked for duplicates and none appear to exist. The product lookup table is just massive.
The only solution I've found so far is to run Lookupvalue formulas to bring in new colums to the sales data.
Hoping someone may have an answer for me.
Thank you,
Turns out there were null values creating this issue. Problem solved.
4 Replies
Turns out there were null values creating this issue. Problem solved.
- parry2k
Super User
Graeme_Webb First, you have to figure out why there are many to many relationships especially when the product table should have unique id to connect to the sales table. The easiest way would be to put UPC code and count of ups from the product table in a table visual and see if there are products that have a count of more than 1, this means these are duplicates and that will help you to clear/fix your data.
Regarding measure, when you have a product dimension table, you should be using columns from the product table like UPC, category, etc etc and your measure should be ALLSELECTED ( Product Table )
✨ Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
Thank you for the response parry2k - I cannot figure out why my tables have a many to many relationship. I took the UPC's and put them into a table to count to determine if there are any duplicates, and I'm not seeing any duplication.
The excel file we're using for the product code is massive and will barely work on excel. Could the overall file size be an issue?
- parry2k
Graeme_Webb Glad to hear. Mark the respective reply as a solution so that others can find it as well.
Follow us on LinkedIn and to our YouTube channel
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
