Calculating Sum of savings not total

DAX DAX Calculations
1

Hi,

Can someone help me with this Dax? I am trying to calculate the total saving of the individual fabrics in this table and not the total but I can’t understand how I get that.

The table is showing me the correct information for example in this product I cut 3 fabrics

401641
406423
411266

I haven’t cut 401641, yet, so there is blank data, but for the other two, there is, and I can see that compared to my Total BOM qty SQM my new total proportional area per part sqm is improved thus, I saved money as shown in the table.

What I want to do in the card is have the total saved on this product, so it should be the total of 12.59 + 31.25 = 43.84 saved on this kit so far, not 2.18k as shown.

I want the total of the 2 numbers in the red box and not the total in bold basically.

image
image1434×769 172 KB

Plataine Vs Strata 2.pbix (815.9 KB)

Thanks, and appreciate the support

Dan

2

Hello @Krays23,

Thank You for posting your query onto the Forum.

In order to achieve the results based on the scenario that you’ve provided. Below is the DAX measure alongwith the screenshot of the final results provided for the reference -

Total Cost Saving - Totals =
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            'Plataine Reports' ,
            Fabrics[Current SAP Raw Material Number] ,
            Products[Current SAP MM Part Number] ,
            Products[Old SAP MM Part Number] ) ,
        "@Totals" ,
        [Total Cost Saving] ) ,
    [@Totals] )

Final Results
Final Results1742×481 77.3 KB

I’m also attaching the working of the PBIX file as well as providing a link of the post pertaining to the topic - “How to Fix Incorrect Totals” for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Plataine Vs Strata 2 - Harsh.pbix (811.3 KB)

EDIT: In case, you want to round up the numbers upto 2 decimals places then wrap the measure alongwith the “ROUNDUP()” function.

Total Cost Saving - Totals =
ROUNDUP(
    SUMX(
        ADDCOLUMNS(
            SUMMARIZE(
                'Plataine Reports' ,
                Fabrics[Current SAP Raw Material Number] ,
                Products[Current SAP MM Part Number] ,
                Products[Old SAP MM Part Number] ) ,
            "@Totals" ,
            [Total Cost Saving] ) ,
        [@Totals] ) ,
    2 )

Final Results v2
Final Results v21750×484 76.9 KB

2 Likes
3

Thank you so much @Harsh

So annoying as you guys make it look simple! and I always think why didn’t I think like that.

So here you just created a virtual table and then SUMX the table ?

Dan

4

Hello @Krays23,

You’re Welcome!!! :slightly_smiling_face:

I’m glad that I was able to assist you with your query.

Yes, a virtual table has been created with the correct totals and then summing them by wrapping it inside the “SUMX()” function.

Thanks and Warm Regards,
Harsh

1 Like
5

@Harsh

How do I adapt your DAX to show results on all selected dates? I know I need to use AllSELECTED but I can’t figure out where to place it. Basically, I need the results to show parts cut and savings on that day that I select from the date filter.

I have changed the matrix columns to values from filter tables as opposed to fact tables before, and now I see all parts cut on all dates, and the filter isn’t working.

Grrrr
Plataine Vs Strata 3.pbix (1.2 MB)

Total Cost Savings = 
ROUNDUP(
    SUMX(
        ADDCOLUMNS(
            SUMMARIZE(
                'Plataine Reports' ,
                'Fabrics From BOM'[Current SAP MM Fabric Number] ,
                'Products From BOM'[Current SAP MM Part Number] ,
                'Products From BOM'[Old SAP MM Part Number]) ,
            "@Totals" ,
            [Total Cost Saving] ) ,
        [@Totals] ) ,
    2 )
6

Hello @Krays23,

The solution provided onto this thread is based on the initial question which was posted.

For the new scenario’s, please create a new thread as per the Forum guidelines.

Thanks and Warm Regards,
Harsh

2 Likes