Forum Discussion

PuzzledSpark's avatar
PuzzledSpark
Frequent Visitor
8 years ago
Solved

Subtotal not correct

I'm using a table visualization in Power BI and I'm finding the subtotal for both of these are not correct:   a) With Sum: AmountQTD = SUM([AmountSignAdj])- if(max(dimPeriodDAX[Month])>3,CALCULATE...
Need Help
  • mattbrice's avatar
    mattbrice
    8 years ago

    Attached is my reply, although i dont' understand why you are totaling the 'AmountYTD' values in 2016 to get 1000.  Seems like you would want the subtotal to be 400 for 2016.  

     

    Sample reply

     

     

  • PuzzledSpark's avatar
    PuzzledSpark
    8 years ago

    This gives me this strange result (AmountYTD is the original dataset, testQTD is the new result:

    I'll continue thinking throught it myself too.

     

    UPDATE:

    This is almost there:

     

    testQTD =
    var _thisQ = TOTALQTD( [Total Amount], dimDates[DAXDate] )
    var _lastQ =  TOTALQTD( [Total Amount] , PREVIOUSQUARTER( dimDates[DAXDate] ), VALUES( dimDates[Year] ) )
    RETURN
    IF(_thisQ = 0,
            BLANK(),
    _thisQ - _lastQ)

     

     

    Just got to figure out how to total 2017.

PuzzledSpark's avatar
PuzzledSpark
Frequent Visitor
8 years ago

Thanks - this is good though I don't fully understand it yet (will have to study it).

 

The YTD was just me repeating the original problem so that we can see a side by side comparison of problem and solution.

 

Thanks for your help, really appreciate the speedy response by the community!

 

For everyone;'s benefit the formula was:

 

testQTD = var _thisQ = TOTALQTD( [Total Amount], dimDates[DAXDate] )
var _lastQ =  TOTALQTD( [Total Amount] , PREVIOUSQUARTER( dimDates[DAXDate] ), VALUES( dimDates[Year] ) )
RETURN
_thisQ - _lastQ

PuzzledSpark's avatar
PuzzledSpark
Frequent Visitor
8 years ago

Actually is there a way of stopping the formula creating a negative entry in the very last + 1 quarter? So "If this quater amount = 0 then do nothing" ?

  • GilbertQ's avatar
    GilbertQ
    Super User
    8 years ago

    Hi PuzzledSpark

     

    You could modify your measure with the following:

     

    testQTD = var _thisQ = TOTALQTD( [Total Amount], dimDates[DAXDate] )
var _lastQ =  TOTALQTD( [Total Amount] , PREVIOUSQUARTER( dimDates[DAXDate] ), VALUES( dimDates[Year] ) )
RETURN
IF(_lastQ = 0, 
        BLANK(),
        _thisQ - _lastQ
   )
    • PuzzledSpark's avatar
      PuzzledSpark
      Frequent Visitor
      8 years ago

      This gives me this strange result (AmountYTD is the original dataset, testQTD is the new result:

      I'll continue thinking throught it myself too.

       

      UPDATE:

      This is almost there:

       

      testQTD =
      var _thisQ = TOTALQTD( [Total Amount], dimDates[DAXDate] )
      var _lastQ =  TOTALQTD( [Total Amount] , PREVIOUSQUARTER( dimDates[DAXDate] ), VALUES( dimDates[Year] ) )
      RETURN
      IF(_thisQ = 0,
              BLANK(),
      _thisQ - _lastQ)

       

       

      Just got to figure out how to total 2017.