Forum Discussion
Calculate stopped filtering - referencing another table
I've been using a slight variation of a measure and today it's not working and I can't figure out why (I hope it's just something silly I'm not seeing).
I have a data list where every single item on an invoice gets its own line. We have promotions that we run and the promotion code ends up on its own line since they treated it like another item. To figure out the total invoice sales for a particular promotion I've been doing the following:
1. Create a new table for the promotion where the item number equals the promotion code. Include the invoice number as one of my columns on this table so now I have a list of every single invoice that contained this promotion.
2. Write a measure where I use Calculate to sum up all the sales values from the main data table where the document number exists in the table I created.
The table 'Virtual Table' looks fine, this is the measure that ends up giving me the sum of all the sales amounts and not the ones that have a matching invoice number to the 'Virtual Table':
Virtual Promotion = CALCULATE(SUM(udv_SalesItemsByLine[Values Sales Amt], 'Virtual Table'[Invoice Number])
Also if you realize a better method to my case above I'm interested.
Thanks - your question triggered me to remember the piece I was missing - just forgot to map the relationship for the new table. My new table has all the unique invoice numbers so that's my 1 and the data source is my many. Working as intended again.
4 Replies
- edhans
Community Champion
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum - amitchandak
Super User
The structure is not clear to me yet. Are these two tables joined with each other and invoice no is unique in virtual table
Then you could have like this
Virtual Promotion = CALCULATE(SUM(udv_SalesItemsByLine[Values Sales Amt]), Values('Virtual Table'[Invoice Number]))Virtual Promotion = sumx(summarize('Virtual Table','Virtual Table'[Invoice Number],"_sum",SUM(udv_SalesItemsByLine[Values Sales Amt])),[_sum])
Else you need a common dimension. If you are creating a table variable in a measure then there is a different way to deal it
- v-gizhi-msft
Community Support
Hi,
Could you please share some sample data, relationships(Linked columns) and expected result as screenshots with me?
And this can let me help you further.
Expect your reply!
Best Regards,
Giotto
Thanks - your question triggered me to remember the piece I was missing - just forgot to map the relationship for the new table. My new table has all the unique invoice numbers so that's my 1 and the data source is my many. Working as intended again.
