Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
SamfarUK
Frequent Visitor

Calculation is not producing the correct figure

‎01-15-2025 03:28 AM

I have a column in the data spreadsheet that = £288k

SamfarUK_1-1736940423841.png

The transformed data in the PBI = £288k 

The measure calculation in the card I have created = £300k

 

SamfarUK_0-1736940281671.png

 

I do not understand how to fix this. I have used the exact same calculation/measure in other reports and not had this problem. 

Labels:
Message 1 of 7
162 Views
0
1 ACCEPTED SOLUTION
danextian
Super User
Super User
In response to danextian

‎01-15-2025 04:22 AM

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" )
)

 

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

Message 5 of 7
103 Views
0
6 REPLIES 6
danextian
Super User
Super User

‎01-15-2025 03:37 AM

hi @SamfarUK 

It seems you are aiming to calculate the sum of values where financial = commitment. In this case, the CALCULATE portion of your measure should do the trick. When using SUMX across the entire Consolidated table, the  CALCULATE expression will be evaluated for each row, and the results will then be summed up.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Message 2 of 7
141 Views
0
SamfarUK
Frequent Visitor
In response to danextian

‎01-15-2025 04:07 AM

hello, thanks for your response!

that does work and now calculates the correct figure, however, it has now broken the slicer element on my page:

 

SamfarUK_0-1736942712876.pngSamfarUK_1-1736942736221.png

whereas previously you can see the cards change in line with the slicer settings:

SamfarUK_2-1736942786694.pngSamfarUK_3-1736942797636.png

 

further help will be much appreciated (i haven't been using PBI very long and am self-taught).

Message 3 of 7
124 Views
0
danextian
Super User
Super User
In response to SamfarUK

‎01-15-2025 04:14 AM

hi @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.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Message 4 of 7
119 Views
0
SamfarUK
Frequent Visitor
In response to danextian

‎01-15-2025 05:07 AM

hi,

so i have the same format of calculation for budget, actual, risks and opps, and, commitments:

SamfarUK_0-1736944529731.png

 

here is the table that feeds commitments (my work network security has limitations on how i can share information)

FinancialLineCost CentreSectionEngineering ManagerPeriodDate Value YearDocument TypeSpend JustificationOrder CategorySpend CategoryPO NumberWO NumberOrder DescriptionVendor Name
Commitmentconfidentialconfidentialconfidentialconfidential131/01/2025    2,453.222025Purchase OrderBAUCorrectivePart - Purchased4552634637 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential130/01/2025    2,453.222025Purchase OrderBAUCorrectivePart - Purchased4552634637 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential130/01/2025    2,453.222025Purchase OrderBAUCorrectivePart - Purchased4552634637 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential124/01/2025    5,877.132025Purchase OrderSpare PartCorrectivePart - Purchased4552661657 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential124/01/2025    5,877.132025Purchase OrderSpare PartCorrectivePart - Purchased4552661657 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential124/01/2025         91.762025Purchase OrderSpare PartCorrectivePart - Purchased4552661657 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential124/01/2025       123.582025Purchase OrderSpare PartCorrectivePart - Purchased4552661657 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential106/01/2025  19,969.162025Purchase OrderSpare PartPreventativePart - Purchased4552670181 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential106/01/2025    7,937.352025Purchase OrderSpare PartPreventativePart - Purchased4552685265 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential131/01/2025  20,400.002025Purchase OrderTBCTBCTBC4552655418 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential131/01/2025    3,729.022025Purchase OrderSpare PartPreventativePart - Purchased4552670708 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential131/01/2025  15,718.412025Purchase OrderTBCTBCTBC4552655408 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential131/01/2025       486.982025Purchase OrderStores ReturnReactivePart - Purchased4552658149 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential131/01/2025    4,727.102025Purchase OrderPlant SecurityPredictiveService4552683651 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential131/01/2025  40,056.902025Purchase OrderSafety InstructionPreventativePart - Purchased4552659195 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential131/01/2025       694.132025Service ContractTBCTBCTBC4552183976 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential110/01/2025    3,724.422025Purchase OrderTBCTBCTBC4552688004 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential207/02/2025    5,688.812025Purchase OrderSpare PartCorrectivePart - Purchased4552661280 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025       356.442025Purchase OrderSpare PartCorrectiveCarriage4552661659 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025       477.902025Purchase OrderSpare PartCorrectiveCarriage4552661659 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025    1,196.202025Purchase OrderSpare PartCorrectiveCarriage4552661659 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025    1,516.282025Purchase OrderSpare PartCorrectiveCarriage4552661659 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025       238.952025Purchase OrderSpare PartCorrectiveCarriage4552661659 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025       124.932025Purchase OrderSpare PartCorrectiveCarriage4552661659 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025       460.122025Purchase OrderSpare PartCorrectiveCarriage4552669024 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025    1,185.542025Purchase OrderSpare PartCorrectiveCarriage4552669024 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025    1,380.362025Purchase OrderSpare PartCorrectiveCarriage4552669024 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025    1,380.362025Purchase OrderSpare PartCorrectiveCarriage4552669024 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025    1,265.142025Purchase OrderSpare PartCorrectiveCarriage4552669024 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025       380.782025Purchase OrderSpare PartCorrectiveCarriage4552669024 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025       151.312025Purchase OrderSpare PartCorrectiveCarriage4552669024 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025    1,143.602025Purchase OrderSpare PartCorrectiveCarriage4552669027 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025         63.992025Purchase OrderSpare PartCorrectiveCarriage4552669027 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025       187.382025Purchase OrderSpare PartCorrectiveCarriage4552669027 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025         69.902025Purchase OrderSpare PartCorrectiveCarriage4552669027 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025       705.882025Purchase OrderSpare PartCorrectiveCarriage4552669027 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025    2,169.122025Purchase OrderSpare PartCorrectiveCarriage4552669027 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025         59.042025Purchase OrderSpare PartCorrectiveCarriage4552669027 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025       592.772025Purchase OrderSpare PartCorrectiveCarriage4552669027 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025       460.122025Purchase OrderSpare PartCorrectiveCarriage4552669027 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential214/02/2025       136.302025Purchase OrderSpare PartCorrectiveCarriage4552669027 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential228/02/2025    9,760.102025Purchase OrderTBCTBCTBC4552535059 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential331/03/2025    2,358.932025Purchase OrderSpare PartCorrectivePart - Purchased4552683934 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential531/05/2025  96,095.742025Purchase OrderBack to StandardsCorrectivePart - Purchased4552633767 confidentialconfidential
Commitmentconfidentialconfidentialconfidentialconfidential531/12/2025  21,975.002025Service ContractBAUPreventativeService4552423344 confidentialconfidential
Commitment0000000/01/1900               -  000000 00
Commitment0000000/01/1900               -  000000 00
Commitment0000000/01/1900               -  000000 00
Commitment0000000/01/1900               -  000000 00
Commitment0000000/01/1900               -  000000 00
Commitment0000000/01/1900               -  000000 00
Commitment0000000/01/1900               -  000000 00
Commitment0000000/01/1900               -  000000 00
Commitment0000000/01/1900               -  000000 00
Commitment0000000/01/1900               -  000000 00
Message 7 of 7
53 Views
0
danextian
Super User
Super User
In response to danextian

‎01-15-2025 04:22 AM

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" )
)

 

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Message 5 of 7
104 Views
0
SamfarUK
Frequent Visitor
In response to danextian

‎01-15-2025 05:39 AM

That has worked! 😄 Thank you so much for your help in such a quick turnaround too! 

Message 6 of 7
65 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

View All