Forum Discussion
subtract columns
Hi there,
I'm very new to Power BI and am sure the answer to this question should be very simple.
I have a matrix that has various brands in the rows and date in the columns. I should clarify that i used the date hierarchy feature to group by year but have filter on a specific month to see what the values were last year vs this year. I then pulled in sale volumes into the values area of the matrix and showed it as a % of the column total to show share. Essentially I have a table showing brand volume share of June 2018 vs June 2017 but I'd like to add a third column that shows the difference between the 2 columns. I tried using the Quick Measure Year-on-Year change but that calculates the percentage change rather than the % difference between the two columns. Is there a way to easily show June 2018 share less June 2017 share?
2 Replies
- Assuming your just dragging the column name into your visual and letting PBI do whatever calculation. What you need to do is write explicit measures. Example
Sales = SUM(table[sales])
Sales 2017 = CALCULATE([Sales],table[year]=2017)
Sales 2018 = CALCULATE([Sales],table[year]=2018)
Delta Sales = [Sales 2017]-[Sales 2018]
Ideally should have date table and then You can take advantage of PBI TIME INTELLIGENCE
Sales period Prior Year =CALCULATE([Sales],SAMEPERIODLASTYEAR(date[date]))
See this tutorial https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/dax-query-language-for-power-bi-and-power-pivot/sameperiodlastyear-comparation-with-previous-year-dax-power-pivot-power-bi/
The. Just add your hierarchy and the Sales and Sales prior years in and then your delta years. Hi evnrob,
Have you solved your problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please share some data sample and your desired output.
Best Regargds,
Cherry
