Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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.
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.
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
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
115 | |
105 | |
95 | |
79 | |
72 |