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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
zaka88
Frequent Visitor

Display measure in subtotal for one row in matrix

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]:

zaka88_0-1618324773617.png

 

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

zaka88_1-1618324915606.png

 

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 

 

1 ACCEPTED SOLUTION

Hi  @zaka88 ,

 

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

v-kelly-msft_0-1618796311590.png

v-kelly-msft_1-1618796320311.png

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

 

Best Regards,
Kelly

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

View solution in original post

8 REPLIES 8
amitchandak
Super User
Super User

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...

zaka88_0-1618331914631.png

 

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:

v-kelly-msft_0-1618475363898.png

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!

 

 

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:

zaka88_0-1618476311189.png

 

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:

v-kelly-msft_0-1618537966543.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

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

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

 

zaka88_0-1618567592096.png

 

thanks a lot for your help

Hi  @zaka88 ,

 

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

v-kelly-msft_0-1618796311590.png

v-kelly-msft_1-1618796320311.png

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

 

Best Regards,
Kelly

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

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

thanks

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors