How to base measure on what is being grouped together?

[Anonymous]

Hi there awesome community!

Ok... so here is my current roadblock.  I have the following data:

Date (Column)

Project (C)

Resource (C)

Fee (C) - SUMS

Cumulative Fee (M)

Also, I have concatenate keys to join with other tables including DayPrjID, PrjEmpID, and DayPrjEmpID.  DayPrjEmpID would represent (at most) a single record of data and is the truly unique key.

Nevertheless, I'm struggling around Cumulative Fee.  I've got a function working:

Cumulative Fee = Calculate(sumx(filter(allselected('CurrentTable'),[PrjEmpID] = max[PrjEmpID] && [Date]<=max([Date]))

What I want to know is how to modify the measure based off how it is calculated.  In the above, I assume the user is calculating based off the lowest level (PrjEmpID for a given date).  However if employee and/or project were not there, I would want it calculated by whatever group is being performed.

I'm hoping there is some function that can return true/false if it is being grouped/displayed.  

Ideally, no other columns shown --- Cumulative Fee = the total of Fee

Date column shown --- Filter on all dates earlier

Projects column shown --- Filter on projects AND all dates earlier

Resource column shown --- Filter on project/resource AND all dates earlier

As always, much thanks!å

[v-jayw-msft · Accepted solution]

Hi @Anonymous 

I doubt there's such function.

Normally we can manually modify the measure as ALLEXCEPT(table,column1)  to ALLEXCEPT(table,column1,column2).

But to calculate the measure dynamically according to which column appear in the visual, no, i don't think it could be done.

I'd like to suggest you to create multiple measures and create a table contains measures' names then use this table as slicer.

Measure = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[name]))

Measure 2 = CALCULATE(SUM('Table'[value]),ALLEXCEPT('Table','Table'[name],'Table'[type]))

Measure 3 = SWITCH(SELECTEDVALUE('Table (2)'[measure]),"measure",[Measure],"measure 2",[Measure 2])

 

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Anonymous Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490