Forum Discussion
SamfarUK
Helper I
Helper ICalculation is not producing the correct figure
I have a column in the data spreadsheet that = £288k The transformed data in the PBI = £288k The measure calculation in the card I have created = £300k I do not understand how t...
Just ignore this. I think I've figured out why.
CALCULATE ( SUM ( 'consolidated'[value] ), 'Consolidated'[Financial] = "Commitment" )This part of the formula
'Consolidated'[Financial] = "Commitment"is internally translated to
FILTER(ALL(Consolidated[Financial]), Consolidated[Financial] = "Commitment")which filters all rows where Financial is "Commitment" and applies this filter across the entire Consolidated[Financial] column. Therefore, unless the filter originates from a different column, the result remains consistent regardless of the value selected in Consolidated[Financial].
Change it to
CALCULATE ( SUM ( 'consolidated'[value] ), KEEPFILTERS ( 'Consolidated'[Financial] = "Commitment" ) )
SamfarUKHelper I
Helper Ihello, thanks for your response!
that does work and now calculates the correct figure, however, it has now broken the slicer element on my page:
whereas previously you can see the cards change in line with the slicer settings:
further help will be much appreciated (i haven't been using PBI very long and am self-taught).
danextian
Super User
Super Userhi SamfarUK
How are those measures being calculated? Can you please post a workable sample data that is not an image? Make sure to include all the necessary columns as the ones in your slicer are simply the values and the PO Number. You may post al link to a sanitized copy of your pbix or an Excel file stored in the cloud.
- danextian
Just ignore this. I think I've figured out why.
CALCULATE ( SUM ( 'consolidated'[value] ), 'Consolidated'[Financial] = "Commitment" )This part of the formula
'Consolidated'[Financial] = "Commitment"is internally translated to
FILTER(ALL(Consolidated[Financial]), Consolidated[Financial] = "Commitment")which filters all rows where Financial is "Commitment" and applies this filter across the entire Consolidated[Financial] column. Therefore, unless the filter originates from a different column, the result remains consistent regardless of the value selected in Consolidated[Financial].
Change it to
CALCULATE ( SUM ( 'consolidated'[value] ), KEEPFILTERS ( 'Consolidated'[Financial] = "Commitment" ) )- SamfarUK
That has worked! 😄 Thank you so much for your help in such a quick turnaround too!
- SamfarUK
hello again! i am hoping you can help me once 🙂
i used your suggestion for my budget card as shown:
however i now have two different budget types, the original is still "OP25" with the second one being "OP25R", hre's my updated slicer:
how can i add "OP25R" to the measure, i can't figure it out 😞
- SamfarUK
hi,
so i have the same format of calculation for budget, actual, risks and opps, and, commitments:
here is the table that feeds commitments (my work network security has limitations on how i can share information)
Financial Line Cost Centre Section Engineering Manager Period Date Value Year Document Type Spend Justification Order Category Spend Category PO Number WO Number Order Description Vendor Name Commitment confidential confidential confidential confidential 1 31/01/2025 2,453.22 2025 Purchase Order BAU Corrective Part - Purchased 4552634637 confidential confidential Commitment confidential confidential confidential confidential 1 30/01/2025 2,453.22 2025 Purchase Order BAU Corrective Part - Purchased 4552634637 confidential confidential Commitment confidential confidential confidential confidential 1 30/01/2025 2,453.22 2025 Purchase Order BAU Corrective Part - Purchased 4552634637 confidential confidential Commitment confidential confidential confidential confidential 1 24/01/2025 5,877.13 2025 Purchase Order Spare Part Corrective Part - Purchased 4552661657 confidential confidential Commitment confidential confidential confidential confidential 1 24/01/2025 5,877.13 2025 Purchase Order Spare Part Corrective Part - Purchased 4552661657 confidential confidential Commitment confidential confidential confidential confidential 1 24/01/2025 91.76 2025 Purchase Order Spare Part Corrective Part - Purchased 4552661657 confidential confidential Commitment confidential confidential confidential confidential 1 24/01/2025 123.58 2025 Purchase Order Spare Part Corrective Part - Purchased 4552661657 confidential confidential Commitment confidential confidential confidential confidential 1 06/01/2025 19,969.16 2025 Purchase Order Spare Part Preventative Part - Purchased 4552670181 confidential confidential Commitment confidential confidential confidential confidential 1 06/01/2025 7,937.35 2025 Purchase Order Spare Part Preventative Part - Purchased 4552685265 confidential confidential Commitment confidential confidential confidential confidential 1 31/01/2025 20,400.00 2025 Purchase Order TBC TBC TBC 4552655418 confidential confidential Commitment confidential confidential confidential confidential 1 31/01/2025 3,729.02 2025 Purchase Order Spare Part Preventative Part - Purchased 4552670708 confidential confidential Commitment confidential confidential confidential confidential 1 31/01/2025 15,718.41 2025 Purchase Order TBC TBC TBC 4552655408 confidential confidential Commitment confidential confidential confidential confidential 1 31/01/2025 486.98 2025 Purchase Order Stores Return Reactive Part - Purchased 4552658149 confidential confidential Commitment confidential confidential confidential confidential 1 31/01/2025 4,727.10 2025 Purchase Order Plant Security Predictive Service 4552683651 confidential confidential Commitment confidential confidential confidential confidential 1 31/01/2025 40,056.90 2025 Purchase Order Safety Instruction Preventative Part - Purchased 4552659195 confidential confidential Commitment confidential confidential confidential confidential 1 31/01/2025 694.13 2025 Service Contract TBC TBC TBC 4552183976 confidential confidential Commitment confidential confidential confidential confidential 1 10/01/2025 3,724.42 2025 Purchase Order TBC TBC TBC 4552688004 confidential confidential Commitment confidential confidential confidential confidential 2 07/02/2025 5,688.81 2025 Purchase Order Spare Part Corrective Part - Purchased 4552661280 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 356.44 2025 Purchase Order Spare Part Corrective Carriage 4552661659 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 477.90 2025 Purchase Order Spare Part Corrective Carriage 4552661659 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 1,196.20 2025 Purchase Order Spare Part Corrective Carriage 4552661659 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 1,516.28 2025 Purchase Order Spare Part Corrective Carriage 4552661659 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 238.95 2025 Purchase Order Spare Part Corrective Carriage 4552661659 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 124.93 2025 Purchase Order Spare Part Corrective Carriage 4552661659 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 460.12 2025 Purchase Order Spare Part Corrective Carriage 4552669024 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 1,185.54 2025 Purchase Order Spare Part Corrective Carriage 4552669024 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 1,380.36 2025 Purchase Order Spare Part Corrective Carriage 4552669024 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 1,380.36 2025 Purchase Order Spare Part Corrective Carriage 4552669024 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 1,265.14 2025 Purchase Order Spare Part Corrective Carriage 4552669024 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 380.78 2025 Purchase Order Spare Part Corrective Carriage 4552669024 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 151.31 2025 Purchase Order Spare Part Corrective Carriage 4552669024 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 1,143.60 2025 Purchase Order Spare Part Corrective Carriage 4552669027 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 63.99 2025 Purchase Order Spare Part Corrective Carriage 4552669027 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 187.38 2025 Purchase Order Spare Part Corrective Carriage 4552669027 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 69.90 2025 Purchase Order Spare Part Corrective Carriage 4552669027 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 705.88 2025 Purchase Order Spare Part Corrective Carriage 4552669027 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 2,169.12 2025 Purchase Order Spare Part Corrective Carriage 4552669027 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 59.04 2025 Purchase Order Spare Part Corrective Carriage 4552669027 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 592.77 2025 Purchase Order Spare Part Corrective Carriage 4552669027 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 460.12 2025 Purchase Order Spare Part Corrective Carriage 4552669027 confidential confidential Commitment confidential confidential confidential confidential 2 14/02/2025 136.30 2025 Purchase Order Spare Part Corrective Carriage 4552669027 confidential confidential Commitment confidential confidential confidential confidential 2 28/02/2025 9,760.10 2025 Purchase Order TBC TBC TBC 4552535059 confidential confidential Commitment confidential confidential confidential confidential 3 31/03/2025 2,358.93 2025 Purchase Order Spare Part Corrective Part - Purchased 4552683934 confidential confidential Commitment confidential confidential confidential confidential 5 31/05/2025 96,095.74 2025 Purchase Order Back to Standards Corrective Part - Purchased 4552633767 confidential confidential Commitment confidential confidential confidential confidential 5 31/12/2025 21,975.00 2025 Service Contract BAU Preventative Service 4552423344 confidential confidential Commitment 0 0 0 0 0 00/01/1900 - 0 0 0 0 0 0 0 0 Commitment 0 0 0 0 0 00/01/1900 - 0 0 0 0 0 0 0 0 Commitment 0 0 0 0 0 00/01/1900 - 0 0 0 0 0 0 0 0 Commitment 0 0 0 0 0 00/01/1900 - 0 0 0 0 0 0 0 0 Commitment 0 0 0 0 0 00/01/1900 - 0 0 0 0 0 0 0 0 Commitment 0 0 0 0 0 00/01/1900 - 0 0 0 0 0 0 0 0 Commitment 0 0 0 0 0 00/01/1900 - 0 0 0 0 0 0 0 0 Commitment 0 0 0 0 0 00/01/1900 - 0 0 0 0 0 0 0 0 Commitment 0 0 0 0 0 00/01/1900 - 0 0 0 0 0 0 0 0 Commitment 0 0 0 0 0 00/01/1900 - 0 0 0 0 0 0 0 0