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 measure code is:

 

 

 

Stock = 
VAR _month = SELECTEDVALUE(MonthTable[MonthCode],201801)
VAR _vYEARMONTH = DATE(LEFT(_month,4),MID(_month,5,2),"1")-1
VAR _YEARMONTH = CONVERT(FORMAT(_vYEARMONTH,"YYYYMM"),INTEGER)
var _value = SWITCH(True(),
 FIRSTNONBLANK(QueryA[CONTRACT_MONTH],1) = _YEARMONTH, 
 var _previousmonths = CALCULATETABLE(VALUES(QueryA[CONTRACT_MONTH]), QueryA[CONTRACT_MONTH]<_YEARMONTH)
 return CALCULATE(sum(QueryA[STOCK]), _previousmonths),
 FIRSTNONBLANK(QueryA[CONTRACT_MONTH],1) < _YEARMONTH, BLANK(),
 FIRSTNONBLANK(QueryA[CONTRACT_MONTH],1) > _YEARMONTH, sum(QueryA[STOCK])
 )
return _value

 

 

 

 
The month code is defined by a page filter:

 

Can someone help with this issue? What i'm doing wrong?

 

Thank you for your time.

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

6 Replies

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

      • v-xulin-mstf's avatar
        v-xulin-mstf
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi rcaeiro, 

         

         I can not reproduce your issue with the screenshot, could you provide sample data?

         

        Best Regards,

        Link

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi rcaeiro,

     

    Try measure as:

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

    Here is the output:

     

     

    Best Regards,

    Link