Multiple filters on a measure with calculated sum

[KatkaS]

Hello,

I'm trying to create a cummulative measure with another filter in it (in my case filter based on role),

but no luck so far, could someone help please?

Current measure, working fine (it is summarizing the amounts)

FTE Cumulative = CALCULATE(sum(table[  Recorded amount ]), FILTER(all(PERIOD[Report Date]),PERIOD[Report Date] <=MAX(PERIOD[Report Date])))

But when I try to add a filter based on the ROLE (e.g. I want to choose only Managers), I run into issues.

And I need a measure, I cannot simply filter in the visual, unfortunately..

Thank you!

Period	Manager	Specialist	Newcomer	Other
2024/09	1		1
2024/08		3		2
2024/07	2	5
2024/06	-1		1	3
2024/05	3	2

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please try creating a data model with having Role Dimension table.

FTE Cumulative = 
CALCULATE (
    SUM ( 'table'[Recorded amount] ),
    FILTER (
        ALL ( PERIOD[Report Date] ),
        PERIOD[Report Date] <= MAX ( PERIOD[Report Date] )
    )
)

[KatkaS]

Thank you @Jihwan_Kim, for your time! If I add a slicer and filter for the roles with slicer, it is working properly, but I need a measure, not filter with a slicer (I need to deduct the result from another calculated measure..).

[Jihwan_Kim]

Hi,

Please let me know how your expected outcome looks like on the power bi report page.

That will help me a lot to step further.

Thank you.

[danextian]

Hi @KatkaS 

What do you mean by this?

But when I try to add a filter based on the ROLE (e.g. I want to choose only Managers), I run into issues.
And I need a measure, I cannot simply filter in the visual, unfortunately..

Also, is the table your raw data?

[KatkaS]

Thank you, @danextian, for quick reply! I mean that I'm not able to create correct measure that would include another filter based on role:

Current measure (working properly): 

FTE Cumulative = CALCULATE(sum(table[  Recorded amount ]), FILTER(all(PERIOD[Report Date]),PERIOD[Report Date] <=MAX(PERIOD[Report Date])))

One of many measures I tried (not working):

FTE Cumulative MANAGERS ONLY = CALCULATE(sum(table[  Recorded amount ]), FILTER(all(PERIOD[Report Date]),PERIOD[Report Date] <=MAX(PERIOD[Report Date]) && FILTER(table, table[  Role ] = "MANAGER"))

Yes, above is more or less my raw data, when I transform it in the Power query, it looks like this (in the table I only have changes in the roles for that  month):

PERIOD	ROLE	VALUE
01/09/2024	MANAGER	1
01/09/2024	SPECIALIST	-1
01/09/2024	NEWCOMER	2
01/09/2024	OTHER	3
01/08/2024	NEWCOMER	1
01/07/2024	SPECIALIST	-5
01/07/2024	NEWCOMER	8