cancel
Showing results for
Search instead for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## Subtracting a once off value from a monthly total

Hi Guys

this is driving me nuts.

I have a a volume of income data. I have summerized this data per month on a calculated colunm

so in effect i have

Month   ¦  Income  ¦ Tax  Ref ¦  New Colum

Jan         ¦ 125648  ¦              ¦   125648

Feb        ¦ 125463   ¦ 256421 ¦  -130958

Mar       ¦ 586458   ¦              ¦  586458

Apr        ¦ 568412   ¦              ¦  568412

What i need to do is take away a set once off  value from Feb only -  but leave all other values the same as uneffected, then i need to do a cumulative sum on the months which i have worked out.

This is needed to take away a tax refund in Feb that has already been allocated back to 2016 and  so should not effect 2017 income but i need to reference it.

Any help would be great as i have gotten close but keep falling at the last hurdle

2 REPLIES 2
Microsoft

Not sure if I understand your requirement correctly. You can get a table like below

Month   ¦  Income  ¦ Tax  Ref ¦

Jan         ¦ 125648  ¦              ¦

Feb        ¦ 125463   ¦ 256421 ¦

Mar       ¦ 586458   ¦              ¦

Apr        ¦ 568412   ¦              ¦

And you want a calculated column in your original table

Month   ¦  Income  ¦ Tax  Ref ¦  New Colum

Jan         ¦ 125648  ¦              ¦   125648

Feb        ¦ 125463   ¦ 256421 ¦  -130958

Mar       ¦ 586458   ¦              ¦  586458

Apr        ¦ 568412   ¦              ¦  568412

You can create a calculated column using the DAX below
New Column  =  table[Income] - table[Tax Ref]

If I have anything misunderstood, please point it out and provide us more detail information, so that we can make further analysis.

Regards,

Charlie Liao

Helper I

Hi Not quite mate but found a different solution, I removed the income figure from the source by using the edit query, finding the cell with the offending value and replacing with a zero.

## Helpful resources

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors