Display measure in subtotal for one row in matrix

[zaka88]

hi

i have matrix with 3 rows and i want display subtotals for each row in matrix but create measure for one row

example:

i need to display Table1[values] for each row except total for date row

for subtotal of date row i need to display Table1[diff]:

but also i need to display Table1[values] in subtotals for rows Group 1 and Group 2:

i was playing with hasonevalue and isinscope functions but no success (i've created measure which displays table1[diff] in all subtotals instead of just in subtotal for date row)

here a link to pbi file

https://drive.google.com/file/d/1cg55TLpfFcP4jyxvpDQwzSaDi2uywXtX/view?usp=sharing

thanks 

[amitchandak]

@zaka88 , I think isinscope should help you 

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[zaka88]

i hope that it helps me, but i have no idea how to use it in right way.

results of using isinscope() are same for group2 row and for date row, but i need to display value just for date row...

[v-kelly-msft]

Hi @zaka88 ,

Create a measure as below:

Measure = 
IF(ISINSCOPE(Table1[group 2]),[diff],
CALCULATE(SUM('Table1'[Value]),FILTER(ALL(Table1),'Table1'[group 1]=MAX('Table1'[group 1]))))

And you will see:

If I misunderstood your point,pls correct me.

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[zaka88]

thanks for your comment.

but i need display [diff] in subtotal for date

and SUM('Table1'[Value]) in all other rows (for values and for subtotals of group1 and group2).



result should be like that:

[v-kelly-msft]

Hi  @zaka88 ,

Create a measure as below:

Measure = 
var _fir= ISINSCOPE('Calendar'[Date])
var _sec=ISINSCOPE(Table1[group 2])
var _thi=ISINSCOPE(Table1[group 1])
return
IF(_fir&&_sec&&_thi, [Values] ,IF((_fir=FALSE())&&_sec&&_thi,[Value at max date]-[Value at min date],IF((_fir=FALSE())&&(_sec=FALSE())&&_thi,CALCULATE(SUM('Table1'[Value]),FILTER(ALL(Table1),'Table1'[group 1]=MAX('Table1'[group 1]))))))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[zaka88]

yeah, almost done. just need change value in subtotal for group2:

thanks a lot for your help

[v-kelly-msft · Accepted solution]

Hi  @zaka88 ,

The two values below have the same meaning ,which means they should keep the same:

So do you want it to be -17 or 1904?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[zaka88]

yes, now i understand that this 2 values have the same meaing.... 

thanks