SUM (Sub-Total) of custom values using Measure

[abdulmannan1991]

Hello. Help needed please! I am unable to calculate the sub-total of custom values.

I created a table (MyTable) with 2 columns, as follows:

Month Number	Month
1	Month 1
2	Month 2
3	Month 3
4	Month 4

I created a What-IF Parameter (slicer) to select the Annual Spend value.

Then, I created the following Measure to calculate the Monthly Spend and to show the total sum:

Monthly Spend = IF(HASONEVALUE(MyTable[Month]), SUM('Annual Spend'[Annual Spend])/12, (SUM('Annual Spend'[Annual Spend])/12)*MAX(MyTable[Month Number]))

I actually need to apply some Savings % to each month separately, and then calculate the Savings per month and the Sum of total savings, so I created 4 What-IF Parameters (slicers) for the selection of monthly savings %. And then I created the following Measure to calculate the monthly savings and sum of total savings:

Total Savings = 
IF(SUM(MyTable[Month Number])<=1, [Monthly Spend] * 'Savings % (Month 1)'[Savings % (Month 1) Value],
    IF(SUM(MyTable[Month Number])<=2, [Monthly Spend] * 'Savings % (Month 2)'[Savings % (Month 2) Value],
        IF(SUM(MyTable[Month Number])<=3, [Monthly Spend] * 'Savings % (Month 3)'[Savings % (Month 3) Value],
            IF(SUM(MyTable[Month Number])<=4, [Monthly Spend] * 'Savings % (Month 4)'[Savings % (Month 4) Value],
               
                ([Monthly Spend] * ('Savings % (Month 1)'[Savings % (Month 1) Value] + 'Savings % (Month 2)'[Savings % (Month 2) Value] + 'Savings % (Month 3)'[Savings % (Month 3) Value] + 'Savings % (Month 4)'[Savings % (Month 4) Value]))

))))

The above Measure gives me correct values for Monthly Savings but doesn't give the correct total sum!! I know the DAX is incorrect in the ELSE statement, but I don't know the correct answer. May be the ELSE statement is wrong, or my whole DAX or Process is wrong for the calculation of monthly sum of savings and the total sum!!

Please can you assist with the correct DAX? Or may be suggest an alternative for the whole DAX/Measure/Process, etc.

For reference, the base file is available on below-given dropbox link.

https://www.dropbox.com/t/US9uDK9V6srQCJwo

Attached a snapshot from the Pbix.

For example, For the Month Number up to 3 and Annual Spend of 1,200,000, and the savings % for month 1, 2 and 3 being 20%, 10% and 5% respectively, the savings per month (in table) are correct, that is, 20,000, 10,000 and 5,000... However the total sum comes out to be 108,000 which is incorrect (and it should ideally be 20,000 + 10,000 + 5,000 = 35,000).  

Thank you.

[amitchandak]

@abdulmannan1991 , try like

Sumx(summarize(MyTable, MyTable[Month Number],"_1",[Total Savings]),[_1])

Sumx(values(MyTable[Month Number]),[Total Savings])

[abdulmannan1991]

Hi @amitchandak 

Super Thanks!! Both Measures work perfectly well.

Do you want me to make any change in my savings Measure (given below)?

I know the ELSE statment is incorrect, hence it doesn't give total SUM.

That is, ([Monthly Spend] * ('Savings % (Month 1)'[Savings % (Month 1) Value] + 'Savings % (Month 2)'[Savings % (Month 2) Value] + 'Savings % (Month 3)'[Savings % (Month 3) Value] + 'Savings % (Month 4)'[Savings % (Month 4) Value]))

Should this be replaced by anything else?

Total Savings = 
IF(SUM(MyTable[Month Number])<=1, [Monthly Spend] * 'Savings % (Month 1)'[Savings % (Month 1) Value],
    IF(SUM(MyTable[Month Number])<=2, [Monthly Spend] * 'Savings % (Month 2)'[Savings % (Month 2) Value],
        IF(SUM(MyTable[Month Number])<=3, [Monthly Spend] * 'Savings % (Month 3)'[Savings % (Month 3) Value],
            IF(SUM(MyTable[Month Number])<=4, [Monthly Spend] * 'Savings % (Month 4)'[Savings % (Month 4) Value],
               
                ([Monthly Spend] * ('Savings % (Month 1)'[Savings % (Month 1) Value] + 'Savings % (Month 2)'[Savings % (Month 2) Value] + 'Savings % (Month 3)'[Savings % (Month 3) Value] + 'Savings % (Month 4)'[Savings % (Month 4) Value]))

))))

[amitchandak · Accepted solution]

@abdulmannan1991 , See if you can simplify it like this

sumx(values(myTable[Month])

Switch ( true(),
max(MyTable[Month Number])<=1,'Savings % (Month 1)'[Savings % (Month 1) ,
max(MyTable[Month Number])<=2,'Savings % (Month 1)'[Savings % (Month 2) ,
max(MyTable[Month Number])<=3,'Savings % (Month 1)'[Savings % (Month 3) ,
max(MyTable[Month Number])<=4,'Savings % (Month 1)'[Savings % (Month 4)
)*[Monthly Spend]

[abdulmannan1991]

@amitchandak  Very helpful. Big thanks!!