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: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

 

 

 

 

 

 

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.

3 Replies

  • 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.