Forum Discussion

rcaeiro's avatar
rcaeiro
New Member
5 years ago
Solved

Matrix column subtotal don't show values

Hello 🙂   I'm doing a matrix table, with one date by column, a date by row and a calculated measure in values: I'm having a problem with the total by column, the value is not showing. The m...
Need Help
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi rcaeiro,

     

    Try measure as:

    Measure = 
SUMX(VALUES(TableA[CONTRACT_MONTH]),[Stock1])

    Here is the output:

     

     

    Best Regards,

    Link

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

You should have a Date table so you don't have to deal with generating your dates inside your measure like that.  Please see this link on how to do that.

Creating a simple date table in DAX - SQLBI

 

Having said that, the SELECTEDVALUE is returning blank in your Total calculation.  You can get around that by using your existing measure in a new measure like this.

 

NewMeasure = SUMX(VALUES(MonthTable[MonthCode]), [Stock])

 

Pat

  • rcaeiro's avatar
    rcaeiro
    New Member
    5 years ago

    Hello Pat, thank you for your feedback.

     

    I've converted the "MonthTable" into a Date Table, and calculated the month code that i want.

    Now i've the measure like that:

     

    Stock = 
VAR _month = SELECTEDVALUE(MonthTable[MonthCode1],201801)
var _value = SWITCH(True(),
 FIRSTNONBLANK(QueryA[CONTRACT_MONTH],1) = _month, 
 var _previousmonths = CALCULATETABLE(VALUES(QueryA[CONTRACT_MONTH]), QueryA[CONTRACT_MONTH]<_month)
 return CALCULATE(sum(QueryA[STOCK]), _previousmonths),
 FIRSTNONBLANK(QueryA[CONTRACT_MONTH],1) < _month, BLANK(),
 FIRSTNONBLANK(QueryA[CONTRACT_MONTH],1) > _month, sum(QueryA[STOCK])
 )
return _value

    And a new measure:

    Stock2 = SUMX(VALUES(MonthTable[MonthCode1]), [Stock])

     

    But the column with total still empty 😞

     

    Do you have any suggestion?