Forum Discussion
Show Gap in Pivot table
Dear Experts,
I want to show sales gap in pivot table in Power Bi.
Raw data is as below.
| FY | Categpry | Sales |
| FY20 | A | 10 |
| FY20 | A | 5 |
| FY20 | B | 20 |
| FY20 | B | 30 |
| FY21 | A | 20 |
| FY21 | A | 30 |
| FY21 | B | 10 |
| FY21 | B | 10 |
And I want to show in Power BI Pivot table as below.
Showing FY20, FY21 sales is simple, but difficult to show sales gap between FY(FY21 vs. FY20)
| Category | FY20 | FY21 | FY21 vs. FY20 |
| A | 15 | 50 | 35 |
| B | 50 | 20 | -30 |
Hi, ParkJE1101
Try to create 3 measures like below:
_FY20 = CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', 'Table'[FY] = "FY20" ) )_FY21 = CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', 'Table'[FY] = "FY21" ) )_FY21_20 = SUMX ( 'Table', [_FY21] - [_FY20] )Then to create a calculate table below:
Table 2 = SUMMARIZE ( 'Table', 'Table'[Category], "FY20", 'Table'[_FY21], "FY21", ' Table '[_FY21], "FY21-20", ' Table '[_FY21_20] )Result:
OR:
To do it in power query:
- Create a table copy
- then group by year and category,
- pivot table,
- finally add a custom column(FY21-FY20)
Please refer to the attachment below for details
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
ParkJE1101 , You need to create three measures. And this case better to have a separate FY Table
like
FY 20 = calculate(sum(Table[Sales]), filter(Table,Table[FY] ="FY20"))
FY 21 = calculate(sum(Table[Sales]), filter(Table,Table[FY] ="FY21"))
diff = [FY 21] -[FY 20]
or
FY 20 = calculate(sum(Table[Sales]), filter(all(FY),FY[FY] ="FY20"))
FY 21 = calculate(sum(Table[Sales]), filter(all(FY),FY[FY] ="FY21"))
diff = [FY 21] -[FY 20]
if you have a date use time intelligence
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA - v-angzheng-msft
Community Support
Hi, ParkJE1101
Try to create 3 measures like below:
_FY20 = CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', 'Table'[FY] = "FY20" ) )_FY21 = CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', 'Table'[FY] = "FY21" ) )_FY21_20 = SUMX ( 'Table', [_FY21] - [_FY20] )Then to create a calculate table below:
Table 2 = SUMMARIZE ( 'Table', 'Table'[Category], "FY20", 'Table'[_FY21], "FY21", ' Table '[_FY21], "FY21-20", ' Table '[_FY21_20] )Result:
OR:
To do it in power query:
- Create a table copy
- then group by year and category,
- pivot table,
- finally add a custom column(FY21-FY20)
Please refer to the attachment below for details
Is this the result you want? Hope this is useful to you
Please feel free to let me know If you have further questions
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
