SUM (Sub-Total) of custom values using Measure

[abdulmannan1991]

Hello. Help needed, please! I can't calculate the subtotal of custom values.

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

Number of months	My
1	My 1
2	My 2
3	My 3
4	My 4

I have created a What-IF (slicer) parameter to select the annual spending value.

Then, I created the following measure to calculate the monthly expense and to show the total sum:

Gasto mensual: IF(HASONEVALUE(MyTable[Month]), SUM('Annual Spend'[Annual Spend])/12, (SUM('Annual Spend'[Annual Spend])/12)*MAX(MyTable[Month Number]))

In fact, I need to apply a savings percentage to each month separately, and then calculate the savings per month and the sum of total savings, so I created 4 What-IF parameters (slices) for the selection of % monthly savings. And then I created the following measure to calculate the monthly savings and the sum of the total savings:

Savingtotals : 
IF(SUM(MyTable[Number of month])<-1, [Monthly Expense] * '% Savings (Month 1)'[% Savings (Month 1) Value],
    IF(SUM(MyTable[Number of Month])<2, [Monthly Expense] * '% Savings (Month 2)'[% Savings (Month 2) Value],
        IF(SUM(MyTable[Month Number])<-3 [Monthly Expense] * '% Savings (Month 3)'[% Savings (Month 3) Value],
            IF(SUM(MyTable[Month Number])<-4, [Monthly Expense] * '% Savings (Month 4)'[% savings (month 4) Value],
               
                ([Monthly expense] * ('Monthly expenses] % 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 does not give the correct total sum!! I know the DAX is incorrect in the ELSE statement, but I don't know the correct answer. It may be that the elSE declaration is incorrect, or all my DAX or Process is incorrect for calculating the monthly sum of savings and the total sum!!

Please, can you help with the right DAX? Or it may be to suggest an alternative for all the DAX /Measure/Process, etc.

For reference, the base file is available in the dropbox link below.

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

A snapshot of the Pbix is attached.

For example, for the number of months to 3 and the annual expense of 1,200,000, and the percentage of savings for month 1, 2 and 3 being 20%, 10% and 5% respectively, the savings per month (in the table) are correct, i.e. 20,000, 10,000 and 5,000... However, the total sum becomes 108,000 which is incorrect (and ideally it is to be 20,000 + 10,000 + 5,000 to 35,000).

Thank you.

[v-xicai · Accepted solution]

Hi @abdulmannan1991 ,

You may create a measure like DAX below to replace your original measure displaying in Table visual .

Total Sum =

VAR _table = SUMMARIZE('MyTable',[Month],"_Value",[Total Savings])

RETURN

IF(HASONEVALUE('MyTable'[Month]),[Total Savings],SUMX(_table,[_Value]))

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@abdulmannan1991 ,

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

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

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]