Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Patryk_PL_92
Helper I
Helper I

Subtract sum of category from a fixed value

Hi All,

 

looking for help to write DAX formula/measure.

Consider data in table below, there is a Fixed Value (Limit) for each Bank (col B).

Bank may spend money throughout the year.

I need to write a formula which will SUBTRACT sum of "Used Amount" by category "Bank Name" FROM fixed value (from col B)  - it should give me one value, showing what amount is left to be used - then I add it to the bar chart.
X axis - Bank name
Y axis - col B and Sum of col D and amount left to be used (calculated from DAX formula) - each bank will have 3 columns in visual.

 

I tried to do that on my own, but because this fixed value from col B is duplicated all over the table, then this is a problem for me to cover this exception in the formula.

I know it would be better to keep this fixed value from col B in separate table like:
Bank A - 100mln
Bank B - 500mln

Bank C - 250mln

but this is the data model with which I have to work and I can not change that 😞

 

Patryk_PL_92_0-1662709917069.png

Patryk_PL_92_1-1662710263969.png

 

1 ACCEPTED SOLUTION

Hi @Patryk_PL_92 
Before jumping to much more complex formulas, would you please try the following

_SumSpentCash = SUM ( Table1[Used Amount] )

_AvgTotalAvailableFunds = AVERAGE ( Table1[Available amount for this year] )

_SubCashLeftToBeUsed = [_AvgTotalAvailableFunds] - [_SumSpentCash]

View solution in original post

4 REPLIES 4
tamerj1
Super User
Super User

Hi @Patryk_PL_92 

Please share the dax formula that you have use? Is it a measure or a calculated column?

What is needed is a measure - single value per Bank Name.

Subtraction between:
[available funds in col B] - [sum of spent cash in col D]

Currently there are no calculated columns in a table, these are raw data from data source.

 

Formula I used:
1st measure (to calculate how much have we spent in total per Bank):
_SumSpentCash = CALCULATE(SUM(Table1[Used Amount]), Table1[Bank Name])

 

2nd measure (to get one amount of available funds per bank. There must be a better way to get that fixed value, but I do not have an idea how to... 😞 😞
_AvgTotalAvailableFunds = AVERAGEX(KEEPFILTERS(VALUES(Table1[Bank Name])), CALCULATE(COUNTA(Table1[Available amount for this year]))

3rd measure (final one - it should show how much cash can we still spend, not to go over the fixed limit):
_SubCashLeftToBeUsed = CALCULATE([_AvgAvailableFunds] - [_SumSpentCash])

Hi @Patryk_PL_92 
Before jumping to much more complex formulas, would you please try the following

_SumSpentCash = SUM ( Table1[Used Amount] )

_AvgTotalAvailableFunds = AVERAGE ( Table1[Available amount for this year] )

_SubCashLeftToBeUsed = [_AvgTotalAvailableFunds] - [_SumSpentCash]

Hi @tamerj1 

After your post I just found my stupid mistake in formulas
1st measure should not have the filter parameter

2nd measure can look as simple as yours or:
_AvgTotalAvailableFunds = AVERAGEX(KEEPFILTERS(VALUES(Table1[Bank Name])), CALCULATE(SUM(Table1[Available amount for this year]) / COUNTA(Table1[Available amount for this year]))

 

Anyway, it works well now
thanks for help 😉

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.