Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
So I habe data like this.
Date. Type Value
19/1/2020 A 12
19/1/2020 B 20
20/1/2020 A 40
20/1/2020 B 20
.
.
I want to calculate A - B and plot it on a graph. I can't write Type = A in a formula to filter as I have many types. But i just want to subtract only two types at once, not more. I tried firstblank and lastblank formula but it doesn't give desire results as it always picks highest and lowest value... I want to make sure I am doing A - B all the time regardless of max or min value.
any help will highly be appreciated
Hi,
This is what I have, should be pretty robust.
Measure =
var _CT = CALCULATETABLE(SUMMARIZE('Table';'Table'[Type];'Table'[Date]);ALLEXCEPT('Table';'Table'[Date]))
var _AValue = CALCULATE(SUM('Table'[Value]);FILTER(_CT; 'Table'[Type]= "A"))
var _BValue = CALCULATE(SUM('Table'[Value]);FILTER(_CT; 'Table'[Type]= "B"))
return
_AValue-_BValue
Looking for this? Please mark as solution. Helpful? Thumbs up would be great.
Kind regards, Steve.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Hello @Anonymous ,
What is the expected result? Will your data has two records for each date?
Cheers!
Vivek
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
Yes every different type (A. B, C) have data for each date.
Expected result
Date Result
19/1/2020. -12
20/1/2020. 20
@Anonymous
You may try this:
Reuslt =
VAR _FirstValue =
FIRSTNONBLANK(
dtTable3[Value],
MAX(dtTable3[Date])
)
VAR _LastValue =
LASTNONBLANK(
dtTable3[Value],
MAX(dtTable3[Date])
)
VAR _Difference = _FirstValue - _LastValue
RETURN
_Difference
Cheers!
Vivek
If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)
Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |