problem in removing Subtotal

[Anonymous]

I have used the below formula for removing the Subtotals. But for Company with single row the subtotal is not removing.

Similarly for the CAP_TYPE column if 2 rows has large then the TOTAL is large. it should be empty.

Formula for removing Sub_Total is 

M_O/S SHARES (m) = IF(HASONEVALUE('Merge2-Used for Earnings Monitor Tbale Format'[Os_Shrs_m]), VALUES('Merge2-Used for Earnings Monitor Tbale Format'[Os_Shrs_m]), BLANK())

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Please create the measures as below to work on it.

A = IF([M_CAP TYPE] = "Large",1,0)

A1 = SUMX('Merge2-Used for Earnings Monitor Tbale Format',[A])

A2 = IF(ISINSCOPE('Merge2-Used for Earnings Monitor Tbale Format'[CONAME]),[M_CAP TYPE],IF([A1]>=2,"Large",BLANK()))

[v-frfei-msft]

Hi @Anonymous ,

If and ISINCOPE function should help you in your scenario. Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[Anonymous]

@v-frfei-msft 

here is the link for .pbix file

https://drive.google.com/file/d/1F6XPTchbhf95m53V-nObyVHC6leL0UTJ/view?usp=drivesdk

[Anonymous]

@Anonymous  Please select matrix click on format pane and turn off the row subtotal option

[Anonymous]

@Anonymous  i need Subtotal for limited column. so that is why i am removing subtotal for the columns which i needed.

[vjnvinod]

@Anonymous 

use "table" instead of "Matrix" (from the visualization pane)

if the data is loaded like this, then use power query and remove those rows