Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Re: Not able to sum by category

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Not able to sum by category

09-08-2021
10:00 AM

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 the result is positive the result should show zero.

Currently, I have the following:

```
balance:=sum(tablename [column A])
LTV:=sum(tablename [column B])
Measure 1:=sumx(tablename,([balance]/[LTV])*.6)-sum(tablename [column A])
Measure 2:=min(sumx(tablename,[Measure 1]),0)
```

Measure 2 works on a line by line i.e. per product. But when I group this by region or colour each region/colour shows 0. For each region I want to add all the outcomes per line where the outcome is lower than 0.

Why is this my DAX code not working as intended, please?

Thanks,

Nav

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2021
09:43 PM

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))

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.

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2021
09:43 PM

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:

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-13-2021
02:44 AM

@Schmidtmayer Thanks for your time and efforts

The solution works well in Power BI Desktop. However, I am working in Excel and found that SELECTEDVALUE is not working in Excel. Is there a way I can change the formula so that it works in Excel?

Thanks,

Naveen

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2021
07:15 AM

Hi All,

Thanks so much for your efforts. I really appreciate it. However, it does not get me the desired result. I thought I make it a bit more clear with demo data.

Let's start with he demo dataset is on the left and while the number do not make much sense I am of course after the DAX code 😊.

See the results:

In the first block (upper left) we calculate the result of the following in Excel:

=(($C2/$D2)*(1-H$1)-$C2*1.6)

in words

((Price / discount) times (1 minus variable)) minus (Price times 1.6)

In the second block (upper right) I calculate

=MIN(H2,0)

the lowest of zero and the result in the first block

In the pivot tables under each block the results are displayed.

In one DAX Column I want achieve what can be seen in the third table i.e. per colour only when the results is below zero

20% | 30% | 40% | |

red | - | -1.22 | - 16.52 |

Blue | - | - | - 11.68 |

Green | - | - 8.57 | - 24.26 |

or as in the pivot table below

The results I getting from the two suggestions above are in the image below. Please note that I am only taking the last outcome i.e. what should be Sum of 40%.

Thanks,

Nav

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-09-2021
12:41 PM

If I got it right now, you have to create 3 Measures.

One for 20%, one for 30% and one for 40%

20 % =

var calc = CALCULATE(((SUM('Sample'[Price]) / SUM('Sample'[Discount])) * (1 - 0.2)) - (SUM('Sample'[Price]) * 1.6))

return

IF(calc > 0, "-", calc)

30 % =

var calc = CALCULATE(((SUM('Sample'[Price]) / SUM('Sample'[Discount])) * (1 - 0.3)) - (SUM('Sample'[Price]) * 1.6))

return

IF(calc > 0, "-", calc)

40 % =

var calc = CALCULATE(((SUM('Sample'[Price]) / SUM('Sample'[Discount])) * (1 - 0.4)) - (SUM('Sample'[Price]) * 1.6))

return

IF(calc > 0, "-", calc)

Then insert all the measures in the table.

With this measures, I get the same table as above:

Regards

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-08-2021
10:26 PM

Hi @naveen73

Try this code to add a new column and use that to cover your request:

New Column =

Var _balance=sum(tablename [column A])

Var _LTV=sum(tablename [column B])

Var _Calc = _balance/_LTV*0.6-_balance

Return

IF(_Calc>=0,0,_Calc)

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

**Appreciate your Kudos**✌️**!!**

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-08-2021
12:51 PM

Try this:

Measure 1 = CALCULATE( ([balance] / [LTV] * 0.6 - [balance]))

Measure 2 = IF( [Measure 1] > 0, 0, [Measure 1])

Regards

Announcements

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Top Solution Authors

User | Count |
---|---|

130 | |

68 | |

33 | |

26 | |

24 |

Top Kudoed Authors

User | Count |
---|---|

139 | |

78 | |

39 | |

39 | |

21 |