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.
Hi Community,
I am very new to Power BI. I just completed my Power BI Beginners and Intermediate courses 3 weeks ago and taking on a small project within my team to convert one report from excel to Power BI. I am the first person in my wider team to use Power BI and really in need of some guidance.
My data has half year and full year results for few companies and I want to build some smarts in my dashboard where users can filter to different periods and see the movements between those selected periods.
For example:
They can select 1H18 v 1H19 or 1H18 v 2H19 and ablet to see the movement between this period.
Below is a sample data of what my data looks like.
Category | Company | Period | Name | Value |
Earnings and Returns | A | 1H18 | Cash earnings | 1,200 |
Earnings and Returns | A | 1H19 | Cash earnings | 2,300 |
Earnings and Returns | A | 2H18 | Cash earnings | 1,108 |
Earnings and Returns | A | 2H19 | Cash earnings | 2,208 |
Earnings and Returns | A | FY18 | Cash earnings | 2,308 |
Earnings and Returns | A | FY19 | Cash earnings | 4,508 |
Earnings and Returns | C | 1H18 | Cash earnings | 121 |
Earnings and Returns | C | 1H19 | Cash earnings | 209 |
Earnings and Returns | C | 2H18 | Cash earnings | 56 |
Earnings and Returns | C | 2H19 | Cash earnings | 269 |
Earnings and Returns | C | FY18 | Cash earnings | 177 |
Earnings and Returns | C | FY19 | Cash earnings | 478 |
Earnings and Returns | E | 1H18 | Cash earnings | 1,251 |
Earnings and Returns | E | 1H19 | Cash earnings | 2,586 |
Earnings and Returns | E | 2H18 | Cash earnings | 2,834 |
Earnings and Returns | E | 2H19 | Cash earnings | 2,968 |
Earnings and Returns | E | FY18 | Cash earnings | 4,085 |
Earnings and Returns | E | FY19 | Cash earnings | 5,554 |
Revenues | A | 1H18 | Net Interest Income | 5,000 |
Revenues | A | 1H19 | Net Interest Income | 6,200 |
Revenues | A | 2H18 | Net Interest Income | 5,212 |
Revenues | A | 2H19 | Net Interest Income | 6,225 |
Revenues | A | FY18 | Net Interest Income | 10,212 |
Revenues | A | FY19 | Net Interest Income | 12,425 |
Revenues | C | 1H18 | Net Interest Income | 321 |
Revenues | C | 1H19 | Net Interest Income | 569 |
Revenues | C | 2H18 | Net Interest Income | 321 |
Revenues | C | 2H19 | Net Interest Income | 520 |
Revenues | C | FY18 | Net Interest Income | 642 |
Revenues | C | FY19 | Net Interest Income | 1,089 |
Revenues | E | 1H18 | Net Interest Income | 7,215 |
Revenues | E | 1H19 | Net Interest Income | 6,958 |
Revenues | E | 2H18 | Net Interest Income | 7,023 |
Revenues | E | 2H19 | Net Interest Income | 6,213 |
Revenues | E | FY18 | Net Interest Income | 14,238 |
Revenues | E | FY19 | Net Interest Income | 13,171 |
Expenses | A | 1H18 | Total Operating Expenses | -2,554 |
Expenses | A | 1H19 | Total Operating Expenses | -2,431 |
Expenses | A | 2H18 | Total Operating Expenses | -2,727 |
Expenses | A | 2H19 | Total Operating Expenses | -2,518 |
Expenses | A | FY18 | Total Operating Expenses | -5,281 |
Expenses | A | FY19 | Total Operating Expenses | -4,949 |
Expenses | C | 1H18 | Total Operating Expenses | -621 |
Expenses | C | 1H19 | Total Operating Expenses | -325 |
Expenses | C | 2H18 | Total Operating Expenses | -423 |
Expenses | C | 2H19 | Total Operating Expenses | -369 |
Expenses | C | FY18 | Total Operating Expenses | -1,044 |
Expenses | C | FY19 | Total Operating Expenses | -694 |
Expenses | E | 1H18 | Total Operating Expenses | -4,215 |
Expenses | E | 1H19 | Total Operating Expenses | -4,689 |
Expenses | E | 2H18 | Total Operating Expenses | -3,689 |
Expenses | E | 2H19 | Total Operating Expenses | -4,578 |
Expenses | E | FY18 | Total Operating Expenses | -7,904 |
Expenses | E | FY19 | Total Operating Expenses | -9,267 |
I totally appreciate your help.
Warm regards,
HagenHawke
Solved! Go to Solution.
Hi @HagenHawke ,
If you want it be dynemic, you will need to create two slicer tables as below:
slicer1 = DISTINCT('Table'[Period])
slicer2 = DISTINCT('Table'[Period])
Then create a measure like below:
Measure =
var selected1 = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Company],'Table'[Name]),'Table'[Period]=SELECTEDVALUE(slicer1[Period])))
var selected2 = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Company],'Table'[Name]),'Table'[Period]=SELECTEDVALUE(slicer2[Period])))
return
(selected1-selected2)/selected2
Since it's a measure, it will be generated under each column if you put it into value field.
Then you could turn off the text wrap and hide the extra columns
Best Regards,
Jay
Hi @HagenHawke ,
If you want it be dynemic, you will need to create two slicer tables as below:
slicer1 = DISTINCT('Table'[Period])
slicer2 = DISTINCT('Table'[Period])
Then create a measure like below:
Measure =
var selected1 = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Company],'Table'[Name]),'Table'[Period]=SELECTEDVALUE(slicer1[Period])))
var selected2 = CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Company],'Table'[Name]),'Table'[Period]=SELECTEDVALUE(slicer2[Period])))
return
(selected1-selected2)/selected2
Since it's a measure, it will be generated under each column if you put it into value field.
Then you could turn off the text wrap and hide the extra columns
Best Regards,
Jay
Thank you so much for taking your time to show me this. This is very helpful for a Power BI Beginner like me. I totally appreciate it.
Kind regards,
M
The out put would look something like below:
User would select different periods like the ones highlighted in 'orange' to see the growth between periods.
Company | Name | 1H18 | 1H19 | 2H18 | 2H19 | FY18 | FY19 | 1H19 v 2H18 | 1H19 v 1H18 | 2H19 v 1H19 | 2H19 v 2H18 | FY19 v FY18 |
A | Cash earnings | 1,200 | 2,300 | 1,108 | 2,208 | 2,308 | 4,508 | 107.6% | 91.7% | -4.0% | 99.3% | 95.3% |
A | Net Interest Income | 5,000 | 6,200 | 5,212 | 6,225 | 10,212 | 12,425 | 19.0% | 24.0% | 0.4% | 19.4% | 21.7% |
A | Total Operating Expenses | - 2,554 | - 2,431 | - 2,727 | - 2,518 | - 5,281 | - 4,949 | -10.9% | -4.8% | 3.6% | -7.7% | -6.3% |
C | Cash earnings | 121 | 209 | 56 | 269 | 177 | 478 | 273.2% | 72.7% | 28.7% | 380.4% | 170.1% |
C | Net Interest Income | 321 | 569 | 321 | 520 | 642 | 1,089 | 77.3% | 77.3% | -8.6% | 62.0% | 69.6% |
C | Total Operating Expenses | - 621 | - 325 | - 423 | - 369 | - 1,044 | - 694 | -23.2% | -47.7% | 13.5% | -12.8% | -33.5% |
E | Cash earnings | 1,251 | 2,586 | 2,834 | 2,968 | 4,085 | 5,554 | -8.8% | 106.7% | 14.8% | 4.7% | 36.0% |
E | Net Interest Income | 7,215 | 6,958 | 7,023 | 6,213 | 14,238 | 13,171 | -0.9% | -3.6% | -10.7% | -11.5% | -7.5% |
E | Total Operating Expenses | - 4,215 | - 4,689 | - 3,689 | - 4,578 | - 7,904 | - 9,267 | 27.1% | 11.2% | -2.4% | 24.1% | 17.2% |
Hi @HagenHawke ,
I am not sure what you mean by movements but you can certainly create a measure that will get the difference between two periods. Please refer to the this pbix -Difference Between Two Periods that are not Date Time Keys
Proud to be a Super User!
Hi @HagenHawke
Can you sketch on paper what you expect the end result to be? This will help someone assist you.
Thank you
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |