Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I have an invoice table which contains a company name, a sale vaue and a date. There's also a filter column which users will toggle on the report.
I'm trying to get a few figures:
1. Difference between earliest and latest price per for each name. I have this as a measure called "Difference"
2. The overall percent change from first to last for all names. This should be -12.46%, and should be just sum of the Difference measure, but sum will not accept a measure. Any ideas?
Raw data example
Name | Value | Filter | Date |
United | 250 | y | 01/01/2020 |
MPC | 90 | y | 01/01/2020 |
SDB | 150 | y | 01/01/2020 |
FPL | 70 | n | 01/01/2020 |
CHEM | 80 | y | 01/01/2020 |
United | 50 | y | 02/01/2020 |
United | 80 | y | 03/01/2020 |
MPC | 1000 | y | 02/01/2020 |
MPC | 100 | y | 03/01/2020 |
MPC | 45 | n | 04/01/2020 |
SDB | 100 | y | 02/01/2020 |
SDB | 200 | y | 03/01/2020 |
FPL | 100 | y | 02/01/2020 |
FPL | 65 | y | 03/01/2020 |
CHEM | 500 | y | 02/01/2020 |
CHEM | 85 | y | 03/01/2020 |
United | 225 | y | 04/03/2020 |
Report example
Name | FirstValue | LastValue | Total Name values | Total all values | Percent total spend | Difference | %GT Difference |
FPL | 70 | 65 | 235 | 3190 | 7.37 | -7.14% | -3.23% |
SDB | 150 | 200 | 450 | 3190 | 14.11 | 33.33% | 15.05% |
United | 250 | 225 | 605 | 3190 | 18.97 | -10.00% | -4.52% |
CHEM | 80 | 85 | 665 | 3190 | 20.85 | 6.25% | 2.82% |
MPC | 90 | 45 | 1235 | 3190 | 38.71 | -50.00% | -22.58% |
Hi @sdb_utd,
Can you please provide your measure formula for test? If will help to test and modify your formula on sample data.
How to Get Your Question Answered Quickly
In addition, you can also take a look at the following blog about measure total issue:
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
https://www.dropbox.com/s/ddvxc6y9psou0du/sample%20data.pbix?dl=0
I think I have this working now. My previous measure for summing the earliest and latest values was just summing the values at the earliest or latest date in the table, ie. summing all values for 01/01/2020 or all values for 04/03/2020, rather than the latest value for a name.
Sum of Total at Latest Date =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
'Table',
'Table'[Name] = ( 'Table'[Name] )
&&
'Table'[Date] = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Name]))
)
)
@sdb_utd ,
I did not get your last column. Rest of the cal is in file.https://www.dropbox.com/s/xc9lp45fx76j1do/FirstLastvalue.pbix?dl=0
I have used firstnonblankvalue and lastnonblankvalue, those are part of lastest release
Appreciate your Kudos.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |