Forum Discussion
Comparison between 2 Categories with Dynamic Slicers
Hello,
I am looking for some help with one of the Reports that i am working on.
The report basically compares the values in 1 Table between different Periods. Here Period is in Text formt (Example : FY20Q1, FY20Q2,...) The Data sits one below the other and the size of data if around 30 Million rows.
I am looking for a help with Data Comparison between different periods and it should give Observations post Comparison. The Observation should in turn be a part of Slicer as well.
The data will be as below:
| Input: | Period | SA Number | Part Number | Price | Qty |
| FY20Q1 | SA1 | PN1 | 0.5 | 2 | |
| FY20Q1 | SA1 | PN2 | 0.4 | 3 | |
| FY20Q1 | SA1 | PN3 | 0.9 | 3 | |
| FY20Q1 | SA1 | PN4 | 0.2 | 3 | |
| FY20Q1 | SA2 | PN1 | 1.2 | 1 | |
| FY20Q1 | SA2 | PN2 | 0.3 | 3 | |
| FY20Q2 | SA1 | PN1 | 0.5 | 5 | |
| FY20Q2 | SA1 | PN2 | 0.4 | 2 | |
| FY20Q2 | SA1 | PN4 | 0.2 | 3 | |
| FY20Q2 | SA1 | PN5 | 0.9 | 3 | |
| FY20Q2 | SA3 | PN1 | 1.2 | 1 | |
| FY20Q2 | SA3 | PN2 | 0.3 | 3 |
and the expected Output is as below:
| FY20Q1 | FY20Q2 | ||||
| Output: | QPA | QPA | Observation | ||
| FY20Q1 Vs FY20Q2 | SA1 | PN1 | 2 | 5 | QPA Increased |
| PN2 | 3 | 2 | QPA Decreased | ||
| PN3 | 3 | PN Deleted | |||
| PN4 | 3 | 3 | No Change in QPA | ||
| PN5 | 3 | PN added | |||
There should be an option to compare between any selected period (Example : FY20Q1 vs FY20Q2 , FY20Q1 Vs FY20Q4 and so on)
Request all the Power BI experts to help me achieve this.
Thanks and Regards,
Mohseen
Hi Anonymous ,
We can create two slicers based on two new tables and three measures to meet your requirement.
1. Create two new tables just containing the distinct period.
Slicer 1 = DISTINCT('Table'[Period])Slicer 2 = DISTINCT('Table'[Period])2. Create three measures and put them to a table visual.
QPA1 = var _selected = SELECTEDVALUE('Slicer 1'[Period 1]) return CALCULATE(SUM('Table'[Qty]),FILTER('Table','Table'[Period]=_selected))QPA2 = var _selected = SELECTEDVALUE('Slicer 2'[Period 2]) return CALCULATE(SUM('Table'[Qty]),FILTER('Table','Table'[Period]=_selected))Observation = IF( ISBLANK([QPA2]),"PN Deleted", IF( ISBLANK([QPA1]),"PN added", IF( [QPA1]<[QPA2],"QPA Increased","QPA Decreased")))If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
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
Anonymous , You need to two period tables. One can be connected. And then refer this blog. Here I have used the date range. You can use period value. In place on Min and Max and use only Max. And filter = max value
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
v-zhenbw-msftCommunity Support
Hi Anonymous ,
We can create two slicers based on two new tables and three measures to meet your requirement.
1. Create two new tables just containing the distinct period.
Slicer 1 = DISTINCT('Table'[Period])Slicer 2 = DISTINCT('Table'[Period])2. Create three measures and put them to a table visual.
QPA1 = var _selected = SELECTEDVALUE('Slicer 1'[Period 1]) return CALCULATE(SUM('Table'[Qty]),FILTER('Table','Table'[Period]=_selected))QPA2 = var _selected = SELECTEDVALUE('Slicer 2'[Period 2]) return CALCULATE(SUM('Table'[Qty]),FILTER('Table','Table'[Period]=_selected))Observation = IF( ISBLANK([QPA2]),"PN Deleted", IF( ISBLANK([QPA1]),"PN added", IF( [QPA1]<[QPA2],"QPA Increased","QPA Decreased")))If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
