Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |