Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Everyone,
I am working on a dashboard and I wanted to remove a column from the over all total, and add it back at the end. How can I accomplish this?
In this instance, I would want to remove "Unallocated Strategic Investments" from the Total, turn that total into a "Sub Total", and then add "Unallocaed Strategic Investments" back into the subtotal to create a new grand total.
@Martin_D Thanks for this. Now, is there a way to add "Unallocated Strategic Invesments to this subtotal and get a new grand total, like the below where the subtotal is the sum of all other entities and then I add back unallocated Strateigc investments?
@mestra25 you would solve more complex scenarios with a mapping table rather than writing more and more DAX code. The mapping table would assign which numbers go into which lines. Think like the numbers being assigned to general ledger accounts. Then the numbers in each line are calculated as a sum of different G/L accounts.
Line
Line ID | Line Label |
1 | Subtotal |
2 | Unallocated Stratgic Investment |
3 | Grand Total |
Mapping (m:n)
Line ID | G/L Account No. |
1 | 111111 |
1 | 222222 |
2 | 333333 |
3 | 111111 |
3 | 222222 |
3 | 333333 |
G/L Account
G/L Account No. | G/L Account Label |
111111 | Account A |
222222 | Account B |
333333 | Unallocated Stratgic Investment |
G/L Entries
Date | G/L Account No. | Amount |
2023-04-07 | 111111 | 300.00 |
2023-04-15 | 222222 | 5.95 |
2023-04-17 | 333333 | 8.00 |
2023-04-17 | 222222 | 150.00 |
2023-04-30 | 111111 | 50.00 |
Example
The relationsships are:
Then the measure is just SUM('G/L Entries'[Amount]) everything else is done by the relationships.
BR
Martin
Hi @mestra25 ,
You need to use the following DAX functions:
Assuming the top level in your rows hierarchy is the column 'Table'[Entity] then this would be the new measure that you would create for the [CM Adj Bgt] measure (for all other measures accordingly):
CM Adj Bgt New Total =
IF (
NOT ( ISINSCOPE ( 'Table'[Entity] ) ),
CALCUALTE (
[CM Adj Bgt],
KEEPFILTERS ( 'Table'[Entity] <> "Unallocated Strategic Investments" )
),
[CM Adj Bgt]
)
BR
Martin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
2 | |
1 | |
1 | |
1 |