Forum Discussion

Dan_W's avatar
Dan_W
Frequent Visitor
8 years ago
Solved

Sum based on filters

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:1...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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

  • Dan_W's avatar
    Dan_W
    8 years ago

    Thank you Lydia -your suggestion worked perfectly.

Dan_W's avatar
Dan_W
Frequent Visitor
8 years ago

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

  • Dan_W's avatar
    Dan_W
    Frequent Visitor
    8 years ago

    Thank you Lydia -your suggestion worked perfectly.