Forum Discussion
Measure to compare data between two files appended into one query and distinguished by 'source name'
Hi, I have appended two files into one query. The source name column has the two source file names (File A & File B). Later in the file, I have price and volume columns. How can I create a measure on power BI to calculate price and volume variance % based on the source name column. For e.g., For File A, Volume = 9000, File B = 8000. Now I want new column with formula 9000-8000/8000. Appreciate any help and guidance. Thank you!
2 Replies
- amitchandak
Super User
Anonymous , if these two are two tables in power bi have some common dimensions date, category , item etc
and then compare them against common dimesion
a measure like
divide(sum(FileA[Volumn]) -Sum(FileB[Volumn]) , Sum(FileB[Volumn]))
refer
https://www.sqlshack.com/implementing-star-schemas-in-power-bi-desktop/
https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/
v-kkf-msftCommunity Support
Hi Anonymous ,
Do your data look like this?
If so, try the following measure or calculated column.
Measure = DIVIDE ( CALCULATE ( SUM ( 'Table'[Volume] ), 'Table'[source name] = "File A" ), CALCULATE ( SUM ( 'Table'[Volume] ), 'Table'[source name] = "File B" ) ) - 1NewColumn = DIVIDE ( CALCULATE ( SUM ( 'Table'[Volume] ), ALL ( 'Table' ), 'Table'[source name] = "File A" ), CALCULATE ( SUM ( 'Table'[Volume] ), ALL ( 'Table' ), 'Table'[source name] = "File B" ) ) - 1If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
