Meaure that groups by a certain column but is still responsive to other slicers

[KK92]

I am trying to calculate costs, you will see in my current measure below that I have 3 variables before I use a return. 

Variable 1: adds 2 columns to my table, development, one that calculates the sum of value where certain conditions are met, and is grouped by the DG (This is the bit that is troublesome to me). The second column is working just fine. 
Variable 2: I calculate a weekly ratio by dividing the two previous columns
Variable 3: I multiply the value column by the weekly ratio
I finally return the SUMX from variable 3

Measure5 =

Var TempTable=ADDCOLUMNS(Development,

"EffortQ",CALCULATE(SUM(Development[Value]),(Development[Cal_LastDateInData]=1&&Development[Quarter]= "Q1"&&Development[Year]= "FY25"),ALLEXCEPT(Development,Development[DG])),

"ScopeBudget",CALCULATE(SUM('Scope/Non Scope %s'[Scope percentage])* SUM(Budgets[Assumed Q1 Cost])-SUM(Budgets[Estimated PI cost Q1])))

VAR TempTable2=ADDCOLUMNS(TempTable,"CostWeek",[ScopeBudget]/[EffortQ])

VAR temptable3=ADDCOLUMNS(TempTable2,"Devcost",IFERROR(Development[Value]*[CostWeek],0))

Return (SUMX(TempTable3,[Devcost]))

 
My main issue here is that in variable 1, I want the sum of value to be grouped by the DG but yet is still subject to a slicer using employee column, I also am using this measure on a matrix with the employee column on it. 

The numbers currently are correct, but for ALL of the table, hence when I add or remove a certain employee I want the "EffortQ" column in variable 1 to update and calculate a whole new cost for each item, which now is not the case as it is static. 

All your comments are already so much appreciated, thank you in advance. 

[v-dineshya]

Hi @KK92 ,

As we haven’t heard back from you, we wanted to kindly follow up to check is your issue resolved or let us know if you need any further assistance

Thanks

[v-dineshya]

Hi @KK92 ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.In case we do not receive a response, we shall proceed to close this thread. For any further discussions or queries, please initiate a new thread in the Microsoft Fabric Community Forum, where we will be glad to assist you.

Thank you.

[Anonymous]

Hi @KK92,

As we haven’t heard back from you, we wanted to kindly follow up to check is your issue resolved or let us know if you need any further assistance

Regards,
Vinay Pabbu

[Anonymous]

Hi @KK92,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Vinay Pabbu

[xifeng_L]

Hi @KK92 

You may need to use VALUES in variable 1 to restore employee filter, you can try the following metrics:

Measure5 =

Var TempTable=ADDCOLUMNS(Development,

"EffortQ",CALCULATE(SUM(Development[Value]),(Development[Cal_LastDateInData]=1&&Development[Quarter]= "Q1"&&Development[Year]= "FY25"),ALLEXCEPT(Development,Development[DG]),VALUES(Development[Employee])),

"ScopeBudget",CALCULATE(SUM('Scope/Non Scope %s'[Scope percentage])* SUM(Budgets[Assumed Q1 Cost])-SUM(Budgets[Estimated PI cost Q1])))

VAR TempTable2=ADDCOLUMNS(TempTable,"CostWeek",[ScopeBudget]/[EffortQ])

VAR temptable3=ADDCOLUMNS(TempTable2,"Devcost",IFERROR(Development[Value]*[CostWeek],0))

Return (SUMX(TempTable3,[Devcost]))

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[KK92]

I'm afraid this still does not yield desired results.

For clarity, I am calculating these Devcost values, which are completely fine but takes in the entire table Development disregarding any filters I apply using the employee column.

What adds to the complexity is that I'm using this measure on a matrix with the employee column on it, and only the employee column. Each employee can belong to multiple DG values and hence, why I need to calculate Costweek for each DG before I multiply by the value in each row of data.

[KK92]

I'm afraid this still does not yield desired results.

For clarity, I am calculating these Devcost values, which are completely fine but takes in the entire table Development disregarding any filters I apply using the employee column.

What adds to the complexity is that I'm using this measure on a matrix with the employee column on it, and only the employee column. Each employee can belong to multiple DG values and hence, why I need to calculate Costweek for each DG before I multiply by the value in each row of data.

[AnkitKukreja]

Hi! @KK92 

You should try All in your measure, you might have to share sample file to look into this further. All will exclude that table/column that you are using in slicer.

[KK92]

Thanks for replying! I can`t share a file I`m afriad (nor replicate one as what I have is quite large, but will start on this)

I`m not sure this is what I am after, I am already using an allexcept which is grouping by DG correctly, whoever, I want that grouped up value to be dynamic (once I add or remove employees, it updates)
I`m using this in two places, on a matrix that has employee column on it, and only that. Other is a simple card where I want to show the total devcost calculated.

[KK92]

Thanks! I won't be able to upload an example I'm afraid, sorry!

I think I'm trying to do the opposite where I already use an Allexcept but I want the measure to be responsive to some other slicers. Please let me know if I missed something here.

[Anonymous · Accepted solution]

Hi @KK92,

I understand that you may not be able to share the PBIX file or any sensitive data, but to reproduce your issue, please provide some sample data (at least 10 rows) so that we can analyze the problem effectively.

Regards,

Vinay Pabbu