Forum Discussion

naveen73's avatar
naveen73
Icon for Helper III rankHelper III
5 years ago
Solved

Not able to sum by category

Hi all, I am trying to make in one column the lower of the following: ([column A] / [column B] * .6) – [column A] or 0    So if the result of the calculation is negative I want that amount. If th...
  • Schmidtmayer's avatar
    5 years ago

    From my point of view, the measure is not working as intended as first of all all columns for price are added, same for discount and then your logic is applied. You want it the other way around. First apply your logic to all single rows, then sum.

    I just recreated your data by manually entering them:

    Next, because I am a huge fan of user interaction and the values of your parameter might change, create a table containing all values of your parameter, your case 0.2, 0.3, 0.4:

    Next, write a measure to store the selection of the value, also asserting a standard value:

    Parameter_value = SELECTEDVALUE(Parameter[value], 0.2) (in case nothing is choosen, 0.2 is returned, assuring a calculation)

    Next, write a measure to calculate your logic, first calculating your formula on every row, summing afterwards, SUMX was made for this:

    Logic = SUMX(Data, IF(Data[price]/Data[discount]*(1-Parameter[Parameter_value])-1.6*Data[price] < 0, -(Data[price]/Data[discount]*(1-Parameter[Parameter_value])-1.6*Data[price]) , 0))

    Next, visualisation:

    Use matrix value, putting a color-product hierarchy in the rows, using Logic as a value, putting column value of Parameter in the filters to switch the parameter, done! 3 screenshots for a all 3 values of your parameter:

    0.2:

    0.3:


    0.4:


    Itmis easy to add new values for the parameter to the table.