Aggregate column values per Client Id in a matrix

[devesqdeves]

Hello!

I am having a hard time dealing with this problem, really hope that someone can help me out.

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

Main goal is to be able to have a slicer with client id that filters the whole matrix , but when  not using the slicer (when the selectedvalue(clientid)=blank()), i have to see the sum approveds for all clients , per manager id.

Context:

- For each ManagerId , i have client ids that have a TotalYearApproved, depending on the client.

- My goal is to aggregate the values of the TotalYearApproved column  per client id, to be like this : 

Being all aggregated , it gives 23.

The values per month are aggregated correctly , but the TotalYearApproved column isnt.

So i have a matrix table :

With the measure :

AggregatedApproveds =

VAR _table = ADDCOLUMNS(

    SUMMARIZE('DW DimGlobalManagerApproved','DW DimGlobalManagerApproved'[TotalYearApproved], 'DW DimGlobalManagerApproved'[ClientId],'Date'[MonthYear]),

    "MonthY",

    CALCULATE(SUM('DW DimGlobalManagerApproved'[Approved]))

)

RETURN

sumx(_table,[MonthY])

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

 If i remove the clientId, the matrix stays like this, it doenst sum the values and only shows the distinct values :

The TotalYearApproved column already calculated before coming to powerbi :

I dont really know what to do in this case.

Thank you all for your patience and attention,

Ricardo

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[devesqdeves]

@lbendlin
Thank you for the reply , i created a sample pbix file explaining my problem

You can download it here :

https://easyupload.io/24jeik

[lbendlin]

you were very close

AggregatedApproveds = 
var a = SUMMARIZE(DimManagerApproved,[ManagerName],[TotalYearApproved],[ClientId],"MonthY",sum(DimManagerApproved[Approved]))
RETURN sumx(a,[MonthY])

Don't forget that CALCULATE forces a context transition.

[devesqdeves]

@lbendlin 
Maybe i didnt explained well , but the expected result should sum the TotalYearApproved column from all clients , and not to be with clientId in the table, like this : 

[lbendlin]

seems to work for me?

[devesqdeves]

Is it not summing the values of TotalYearApproved column 😕

[lbendlin]

Of course not, that field is part of the rows, not the values.  That is not possible with the standard visual, unless you use the column totals for it (but then it would be on the far right)

[devesqdeves]

is there a way to make it possible , using another visual? to have the total in the left?

[lbendlin]

Yes, but I would not recommend that. It will be a tremendous effort both now and for maintenance later, with no flexibility.  Rather consider using separate visuals.

[devesqdeves]

@lbendlin 

Oh i see , thanks a lot for your help and for your time.
If you dont mind just helping me with another subject that is getting my head rolling and i dont understand why, i would really appreciate.

- I want to count the rows of DW FCTEndOfYearEndings , where [FilterDateHCFACCurrentDate] = 1 and  the 'DW FCTEndOfYearEndings'[CandidateId] is different from the DW LeaveDayDim'[CandidateId] (with no relationship) where [FilterDateLeavesCurrentDate]=1)

- This is the measure i am using to count , but is giving me blank
CountRows = 

CALCULATE(

    COUNTROWS('DW FCTEndOfYearEndings'),

    FILTER(

        'DW FCTEndOfYearEndings',

        [FilterDateHCFACCurrentDate] = 1 &&

        NOT(

            CONTAINS(

                'DW FCTEndOfYearEndings',

                'DW FCTEndOfYearEndings'[CandidateId],

                CALCULATE(values('DW LeaveDayDim'[CandidateId]),FILTER('DW LeaveDayDim',[FilterDateLeavesCurrentDate]=1))

            )

        )

    )

)

Measures :

FilterDateHCFACCurrentDate =

VAR _table =

if(SELECTEDVALUE('Date'[Year])=BLANK() && SELECTEDVALUE(MonthSorted[MonthName])=BLANK(),

            ((MAX ('DW FCTEndOfYearEndings'[ProjectPlayerRealEndDate])  >= TODAY() || MAX ('DW FCTEndOfYearEndings'[ProjectPlayerRealEndDate]) = BLANK())

                && MAX ('DW FCTEndOfYearEndings'[TrueStartDate])  <= TODAY()),

            ((MAX ('DW FCTEndOfYearEndings'[ProjectPlayerRealEndDate])  >= _date || MAX ('DW FCTEndOfYearEndings'[ProjectPlayerRealEndDate]) = BLANK())

                && MAX ('DW FCTEndOfYearEndings'[TrueStartDate])  <= _date ))

RETURN

    IF (_table, 1, 0 )

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

FilterDateLeavesCurrentDate =

VAR _table =

if(SELECTEDVALUE('Date'[Year])=BLANK() && SELECTEDVALUE(MonthSorted[MonthName])=BLANK(),

            max('DW LeaveDayDim'[Day])=TODAY(),

            max('DW LeaveDayDim'[Day])=MAX ( 'Date'[Date] ))

RETURN

    IF (_table, 1, 0 )

Can you take a look please ?

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[devesqdeves]

@lbendlin Sure thing.

See the example below , it has more detailed information.

https://easyupload.io/asni50

Thanks a lot for your time , i really appreciate it.

[lbendlin]

You are trying to use a measure as a table filter.  You need to first add that measure to each row of the table (or to an aggregation) before you can filter on it.

[devesqdeves]

@lbendlin How so ?

[lbendlin]

FILTER(
        'DW FCTEndOfYearEndings',
        [FilterDateHCFACCurrentDate] = 1 ...

[devesqdeves]

@lbendlin But thats what i have in the measure , 

Measure_=

CALCULATE(

    COUNTROWS('DW FCTEndOfYearEndings'),

    FILTER(

        'DW FCTEndOfYearEndings',

        [FilterDateHCFACCurrentDate] = 1 &&

        NOT(

            CONTAINS(

                'DW FCTEndOfYearEndings',

                'DW FCTEndOfYearEndings'[CandidateId],

                CALCULATE(values('DW LeaveDayDim'[CandidateId]),FILTER('DW LeaveDayDim',[FilterDateLeavesCurrentDate]=1))

            )

        )

    )

).

What should i do differently?