Forum Discussion

abdulmannan1991's avatar
abdulmannan1991
Icon for Helper II rankHelper II
6 years ago
Solved

SUM (Sub-Total) of custom values using Measure

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 ...
Need Help
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    6 years ago

    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]

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

abdulmannan1991 , try like

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

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

abdulmannan1991's avatar
abdulmannan1991
Icon for Helper II rankHelper II
6 years ago

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's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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]