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,
I have data comprised of running totals that looks like below and I'm trying to find totals for the asset when filtering for asset and dates.
DATE ASSET QTY 1/1/18 12:00 AM B 1 1/1/18 1:15 AM A 8 1/1/18 5:17 AM A 13 1/1/18 9:12 AM B 18 1/1/18 12:43 PM B 24 1/1/18 2:09 PM A 31 1/1/18 2:19 PM B 37 1/1/18 6:29 PM B 42 1/1/18 7:12 PM A 50 1/1/18 10:28 PM A 51 1/2/18 12:22 AM A 56
Since the quanties are running totals - I have used the MAX-MIN to find interval total when filtering over dates for a single asset, but I haven't been able to calculate a sum for both assets.
With my Measure ( Interval Total = [MaxValue] - [MinValue]) it is simly taking the MAX and MIN values so it will work with one "ASSET" selected, but not both.
How do I create a measure that will sum the "ASSET" categories so i will have a total of 5,000 in the calculation above?
I can share a file, but it is kind of difficult from my current location.
Thanks for any advice,
Dan
Solved! Go to Solution.
@Dan_W,
Please check if the following DAX returns your expected result. If not, please share source file via OneDrive.
Grand Total = var MaxValue = MAX(Sheet1[QTY]) var MinValue = MIN(Sheet1[QTY]) var Interval Total = MaxValue - MinValue Return SUMX( CALCULATETABLE( ALLSELECTED(Sheet1[ASSET])), Interval Total)
Regards,
Lydia
Thank you Lydia -your suggestion worked perfectly.
I have gotten a little closer:
My inital measures were:
MaxValue = MAX(Sheet1[QTY]) MinValue = MIN(Sheet1[QTY])
Interval Total = [MaxValue] - [MinValue]
Giving me this:
I tried using SUMX to iterate a table like above - and can now get the totals (i'm not sure if that is exactly what is going on?)
Grand Total = SUMX( CALCULATETABLE( ALLSELECTED(Sheet1[ASSET])), ([MaxValue]-[MinValue]))
Somehow I got where I intended - not really sure how, but is there a way to clean my "Grand Total" measure?
How can I get rid of the intermediary steps of having the MIN and MAX measures?
When I exchange the measures [Maxvalue] for the underlying calculation MAX('sheet1[QTY]) - my results are incorrect?
Thanks
@Dan_W,
Please check if the following DAX returns your expected result. If not, please share source file via OneDrive.
Grand Total = var MaxValue = MAX(Sheet1[QTY]) var MinValue = MIN(Sheet1[QTY]) var Interval Total = MaxValue - MinValue Return SUMX( CALCULATETABLE( ALLSELECTED(Sheet1[ASSET])), Interval Total)
Regards,
Lydia
Thank you Lydia -your suggestion worked perfectly.
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |