Total without one colum

[Ania26]

Hello, 
I would like to create measue which calculates Total for each State but Grand Total shows sum for all States except for values from column A:

State	A	B	C	D	Grand Total	Should be
California	1	1	1	1	4	3
Colorado	2	2	2	2	8	6
Connecticut	3	3	3	3	12	9
Delaware	4	4	4	4	16	12
Florida	5	5	5	5	20	15
Illinois	6	6	6	6	24	18
Grand Total	21	21	21	21	84	63

[danextian · Accepted solution]

Hi @Ania26 

Assuming your actual data is unpivoted (A, B, C, D are in a single column with another column for their corresponding values), try:

measure without A in total = 
IF (
    NOT ( HASONEVALUE ( 'table'[ABCD colummn] ) ),
    CALCULATE ( [your measure], KEEPFILTERS ( 'table'[ABCD colummn] <> "A" ) ),
    [your measure]
)

[Tahreem24]

@Ania26  Create a Measure like mentioned below:

Grand Total = SUM('Table'[B])+SUM('Table'[C])+SUM('Table'[D])

[GrowthNatives]

Hi @Ania26 ,
Use ISINSCOPE (detects whether a row value is in scope) and CALCULATE with ALL(...)[Column] <> "A".

1. Create this measure (replace table/column names with yours):

Total_Adjusted =
VAR Base = SUM( 'YourTable'[Value] )                -- per-cell total
VAR IsGrand = NOT( ISINSCOPE( 'YourTable'[State] ) ) -- true for the bottom grand total row
RETURN
IF(
    IsGrand,
    CALCULATE( Base,
               ALL( 'YourTable'[ColumnName] ),       -- remove any column filters
               'YourTable'[ColumnName] <> "A"        -- exclude column A
    ),
    Base
)

2. Put Total_Adjusted in your Matrix values

- Per-state rows will show the normal sum (A+B+C+D).

- The Grand Total row (bottom) will show the overall sum excluding column A (i.e. totalAllColumns − totalA).

3. Notes / variants

- If you have multiple row levels, detect the exact subtotal level with ISINSCOPE on the correct field, or use HASONEVALUE('YourTable'[State]) if appropriate.

- If your columns are actual separate numeric fields (not a single ColumnName pivot), unpivot in Power Query or sum them explicitly in Base (Base = 'T'[A]+'T'[B]+...).

- If you want to also exclude A from intermediate column subtotals, adjust the IsGrand test accordingly.

⭐Hope this solution helps you make the most of Power BI! If it did, click 'Mark as Solution' to help others find the right answers.
💡Found it helpful? Show some love with kudos 👍 as your support keeps our community thriving!
🚀Let’s keep building smarter, data-driven solutions together!🚀 [Explore More]