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.
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
Solved! Go to Solution.
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.
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.
@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
Covering 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 | |
95 | |
83 | |
70 | |
66 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |