Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rcaeiro
New Member

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:

rcaeiro_0-1612812862181.png

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:
imagem.png

 

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

 

Thank you for your time.

1 ACCEPTED SOLUTION
v-xulin-mstf
Community Support
Community Support

Hi @rcaeiro,

 

Try measure as:

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

Here is the output:

v-xulin-mstf_0-1613551512220.png

 

 

Best Regards,

Link

View solution in original post

6 REPLIES 6
v-xulin-mstf
Community Support
Community Support

Hi @rcaeiro,

 

Try measure as:

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

Here is the output:

v-xulin-mstf_0-1613551512220.png

 

 

Best Regards,

Link

Thank you @v-xulin-mstf that solution solved my problem. 😁

mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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?

Hi @rcaeiro

 

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

 

Best Regards,

Link

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.