Forum Discussion
Total without one colum
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 |
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] )
3 Replies
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] )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]
