Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
azakir
Resolver I
Resolver I

Calculate SUM based on different columns

Hi Guys.  

Feels like mental block, but I can't seem to work out a simple sum. 

 

So here's what I am after: 

A formula that should give me a sum of the budget based on dates, work oders. 

In the example below, for the same site, I would like 07/2022 sum to be 2850+200 = 3050 and 08_2022 sum to be 6050. 

azakir_0-1663570064830.png

When I try changing the column value to SUM, it gives a weird summation: 

 

azakir_1-1663570190667.png

Any help would be appreciated. Thank you

3 ACCEPTED SOLUTIONS

Cheers @Anonymous 

View solution in original post

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

You are welcome.  Attached.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

13 REPLIES 13
Anonymous
Not applicable

Hi @azakir 

try

CALCULATE(
    SUM(Table1[Model WO Budget]),
    FILTER(
        ALLSELECTED(Table1[WO_Status_Change_MK_Date])
    )
)


BR

Thank @Anonymous 

But I am getting the following error: 

 

azakir_1-1663577514500.png

 

Anonymous
Not applicable

Hi @azakir ,

Please try this instead

CALCULATE(
    SUM(Table1[Model WO Budget]),
        ALLSELECTED(Table1[WO_Status_Change_MK_Date])
)
    

Thanks @Anonymous 

The formula worked, and it's giving me a SUM. 

azakir_0-1663629954583.png

What I am really after is SUM of "Don't Summarize" Model WO Budget. 

So for July, it should be 2850+200 = 3050

Hi,

Share the download link of your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur 

Thank you for that. The result looks exactly what I was after. But I can't seem to download the file. Can you please share through another link. 

You are welcome.  Attached.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks @Ashish_Mathur . Worked a treat. 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi  

Step 1: Create a calculated tableTable = SUMMARIZE(Sheet1,Sheet1[Model WO Budget],'Calendar'[Period],Sheet1[Work_Order])
Step 2: Create a MeasureMeasure = CALCULATE( SUM('Table'[Model WO Budget]), ALLSELECTED('Table'[Work_Order],'Table'[Model WO Budget]) )
Step 3: Visualize on Matrix

BR If that helps please mark this as a solution

 

 

 

Cheers @Anonymous 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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