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

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.