topic Re: Subtracting row wise value from same column and showing the result as difference in new column in DAX Commands and Tips

Subtracting row wise value from same column and showing the result as difference in new column

aatish178 — Mon, 15 Apr 2024 05:09:12 GMT

Hi Team,

I have a table consisting of Fiscal year FY20 and FY21(both FYs starts from July) and for that I have count of employee ID column next to it...

Now I want to subtract count of empid for FY20 from FY21 without hardcoding the FYs in DAX. In future if FY22 comes then the subtraction should happen from FY21 to FY22..And so on

I have used EARLIER function but no luck yet.

Please help me if something can be done in such case

Re: Subtracting row wise value from same column and showing the result as difference in new column

quantumudit — Mon, 15 Apr 2024 07:19:56 GMT

Hey aatish178

It would be great if you could provide a small sample dataset along with the expected solution dataset/image.

Best Regards,
Udit

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Re: Subtracting row wise value from same column and showing the result as difference in new column

aatish178 — Mon, 15 Apr 2024 07:51:24 GMT

Hi,

Yes

------------------------------------

FY | Count of EmpID | Difference

-------------------------------------

FY20 | 100 | 0 (which is 100-100)

FY21 | 200 | -100 (100 - 200)

FY22 | 50 | 150 (200-50)

Hope this dataset helps

Re: Subtracting row wise value from same column and showing the result as difference in new column

quantumudit — Mon, 15 Apr 2024 11:00:47 GMT

Is this the dataset you have? or,is this a sample of the solution you want?

Best Regards,
Udit

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Re: Subtracting row wise value from same column and showing the result as difference in new column

aatish178 — Mon, 15 Apr 2024 11:49:32 GMT

FY and Count of Emp ID column I have as a dataset and I want to calculate difference from Count of Emp ID column and want to show it in separate column named "difference"

Re: Subtracting row wise value from same column and showing the result as difference in new column

quantumudit — Mon, 15 Apr 2024 17:27:34 GMT

Hi aatish178

You may use the following DAX formula to create a calculated column in the table to dynamically get the difference in employee count from the previous year

Difference = // Get Current and Previous Year VAR _currentFY = CONVERT ( RIGHT ( DiffTable[FY], 2 ), INTEGER ) VAR _previousFY = _currentFY - 1 // Get Employee ID count for current year VAR _empCurrentFY = CALCULATE ( SUM ( DiffTable[Count of EmpID] ), FILTER ( DiffTable, DiffTable[FY] = "FY" & _currentFY ) ) // Get Employee ID count for previous year VAR _empPreviousFY = CALCULATE ( SUM ( DiffTable[Count of EmpID] ), FILTER ( DiffTable, DiffTable[FY] = "FY" & _previousFY ) ) RETURN IF( ISBLANK(_empPreviousFY), 0, ABS ( _empPreviousFY - _empCurrentFY ) ) // Absolute difference in employee count

Here is the screenshot of the solution

I hope this will help but, let me know if anything.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Re: Subtracting row wise value from same column and showing the result as difference in new column

aatish178 — Mon, 15 Apr 2024 17:35:25 GMT

Hi Udit,

Thanks for your reply...I am trying the same in my solution...I will get back to you ☺️

Re: Subtracting row wise value from same column and showing the result as difference in new column

aatish178 — Mon, 15 Apr 2024 18:08:20 GMT

Hi Udit...from screenshot it seems this solution works for future new FYs as well, if yes then that's what I was exactly looking for...also I have just started with power bi work before 3 weeks and here from the solution it seems the filter will not get applied to this result...is there a way to make slicer selection possible on this?

Re: Subtracting row wise value from same column and showing the result as difference in new column

quantumudit — Mon, 15 Apr 2024 17:58:41 GMT

Hello aatish178

It's Udit actually and not Uday 😃
Yeah, the formula is future-proof... so, as the new FY comes, the formula should work.

If the solution worked, then, please mark it as a solution to help others find the solution quickly.

Best Regards,
Udit

Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Re: Subtracting row wise value from same column and showing the result as difference in new column

aatish178 — Mon, 15 Apr 2024 18:21:30 GMT

Sorry my bad...I had mistakely typed a wrong name, I had very hectic day mostly trying the different possibilities for the solution...but finally it worked because of you...thanks again...just a small doubt... I want the slicers to get applied on this solution...may I know what to do for it? Because I had seen the number is unchanged after slicer selection

Re: Subtracting row wise value from same column and showing the result as difference in new column

quantumudit — Mon, 15 Apr 2024 18:28:58 GMT

Hi aatish178

If the field that you are choosing for a slicer is in the same table then, it should filter the table. If the slicer field belongs to some other field, then, you have to make sure that you have an active relationship between the tables

Re: Subtracting row wise value from same column and showing the result as difference in new column

aatish178 — Mon, 15 Apr 2024 18:37:56 GMT

I will look into it 🙂

Re: Subtracting row wise value from same column and showing the result as difference in new column

aatish178 — Tue, 16 Apr 2024 06:39:55 GMT

Hi Udit...the slicer selection is changing the resulting table but the calculated column value is still static. Is there a way to make the calculation which will respond to slicer selection. I tried using calculated measure but for both the FYs I am getting same no. Of employees., and the measure is not calculating the difference between adjacent FY Emp ID count.

Re: Subtracting row wise value from same column and showing the result as difference in new column

quantumudit — Tue, 16 Apr 2024 06:42:32 GMT

Hi aatish178

The slicer should filter things properly. Please provide a sample table that you are using to filter the existing table... I am pretty sure, the issue is in relationships but, yeah I need to see the data model (maybe a screenshot will do)

Re: Subtracting row wise value from same column and showing the result as difference in new column

aatish178 — Tue, 16 Apr 2024 07:28:05 GMT

Hi, I am using single table only and all the calculations belongs to the same table. Here in front end when I added the calculated column in the table object then the value which came is not changing based on different selections made through different slicers. When I checked the issue , I got to know that only measures will respond to different selections and not the calculated column

Re: Subtracting row wise value from same column and showing the result as difference in new column

aatish178 — Tue, 16 Apr 2024 07:34:39 GMT

see here is the output without applying any slicer and this output is correct.

Re: Subtracting row wise value from same column and showing the result as difference in new column

aatish178 — Tue, 16 Apr 2024 07:37:02 GMT

here I have applied slicer selection of country and for that emp count is getting changed but Difference column value is still STATIC and here is the issue coming

Re: Subtracting row wise value from same column and showing the result as difference in new column

quantumudit — Tue, 16 Apr 2024 08:03:09 GMT

There is something fundamentally incorrect in how you are doing it or how you have provided the problem...

It seems you have a relation between the table where you have FY and employee info and a table where you have country info.

I suspect the dataset you have provided initially is incomplete.

You need to understand, that Power BI runs on top of a data model and so as DAX. If you are unsure about how the relationship works and how a slicer filters the whole or, a part of the table, I urge you to provide the anonymized version of the actual datasets you are working with along with the relationships.

If you have a table with the following columns: FY, Count of Employee, and Difference (calculated column) and you are saying the country slicer is filtering the "Employee Count" and not the "Difference" then, this can't be true.

Well, another distant possibility could be, that you are unfamiliar with the Power BI terminologies and you are considering "Table Visual" or, "Matrix Visual" as a table. Well if this "distant possibility" is the case then, I would highly recommend getting familiar with the Power BI terminologies otherwise the results can be drastically different... because a "Table visual" works differently than a "table".

Best Regards,
Udit

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Re: Subtracting row wise value from same column and showing the result as difference in new column

aatish178 — Tue, 16 Apr 2024 09:39:37 GMT

The whole problem is with Count of Emp ID column...it is not a straight forward column used directly... 1stly in the table visual I am using emp Id as count of emp I'd to get count for each FY and on the top of that the difference is getting calculated. Hence problem is arising in the selection. I am using simple table visual and not the Matrix one

Re: Subtracting row wise value from same column and showing the result as difference in new column

quantumudit — Tue, 16 Apr 2024 10:49:52 GMT

Now I got it..

You shouldn't call "Table Visual" as a "Table"... These are 2 different things and again you shouldn't ask Power BI questions by referring to visual.

The way you put your question is incorrect. You should have provided the data tables (In power bi terminology, when someone says "Table" we understand it as a data table and not the table visual) and the kind of relationship you have and then, you should have asked what kind of visual you want with what columns.

Since, you clarified it now. I would require a sample of all the tables (data tables not the table visual), also you need to mention the kind of relationship you have between the tables (if you have multiple data tables) and then, what the results you want ( In your case, I guess the result you want is a table visual with FY, employee count and different and a slicer to filter by country).

Kindly attach the datasets (or, send a link to download a sample PBIX) and your requirements with accurate terminologies.

Please go through this blog to understand how to ask your questions and what resources you need to provide to get your answers quickly and in one shot:

https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Udit

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree