need help on filter measure

[YBZ]

Hi all,

I have a data structure which looks like the below. 

Now I would like to have a measure that gives me the below result. When I would filter a department, I need to see the total hours of the project they were involved in. For Example, when I would filter Department A, I would need to see 700 hours (total hours of Factory X).

Anybody who can point me to the right direction?

Thank you in advance,

Regards

[v-janeyg-msft]

Hi, @YBZ 

Your desired result itself is ambiguous, how does department a include the project hours in department b??

Unless your data is like this:

Do you understand?

Unless you filter factory x then you will get the sum of hours for department a and b.

If you still need help, please modify the requirements and feel free to ask me.

Best Regards,

Community Support Team _Janey

[YBZ]

Hi @v-janeyg-msft , @sabilahmed  , @amitchandak 

Many thanks for your reply.

The example in my first post is perhaps not fully clear because I couldn't share the full data. I now tried to duplicate my data with a sample in PBI. The data in PBI is slightly different, but perhaps now more clear for you what my desired result is.

PBIX file

[v-janeyg-msft]

Hi, @YBZ 

According to your requirement in the sample, you just need to modify the measure.

Like this:

rate per WBS allocation =
IF (
    SELECTEDVALUE ( 'Allocation Key Hours'[Allocation Key] ) = "AJBC",
    SUM ( 'WBS cost'[Cost] )
        / CALCULATE (
            SUM ( 'Allocation Key Hours'[Hours] ),
            ALL ( 'Allocation Key Hours' )
        ),
    SUM ( 'WBS cost'[Cost] ) / SUM ( 'Allocation Key Hours'[Hours] )
)

Best Regards,

Community Support Team _Janey

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

[YBZ]

Hi @v-janeyg-msft ,

Thanks

This seems to work on first sight, but when I filter on AJB4 it shows me 1000 which is not what I want.

As AJB4 is also linked to C.000.123 it should show an end-result of 500 (as it would need to include all hours linked to C.000.123 and therefore it should GROUP the hours of AJB4 and AJBC).

So what the measure would need to do is somehow group all allocation keys linked to a specifc WBS. When filtering on one allocation key, it should take the cost of this WBS divided by all hours of the grouped allocation keys.

so in the case of filtering AJB4, the result should be : 50000 / (30+20+50) 

[v-janeyg-msft · Accepted solution]

@YBZ 

I wrote it according to the needs described in your sample......You didn't mention AJB4.

If you want all to show 500, you just need to truncate part of my original formula.

SUM ( 'WBS cost'[Cost] )
        / CALCULATE (
            SUM ( 'Allocation Key Hours'[Hours] ),
            ALL ( 'Allocation Key Hours' )
        ),

Best Regards,

Community Support Team _Janey

[sabilahmed]

Try this:

Combined =
SUMMARIZE (
'Project',
Project[Project],
"Department", CONCATENATEX ( Project, Project[Department], "," ),
"Hours", SUM ( Project[Hours] )
)

I think the approach you have to take is to group the departments per project. To get the following result:

Combined Project Total Hours
A,B         Factory X         700
C            Factory C         200

So even if you slice on A, you should get total 700 hours.

Hope this works.

[amitchandak]

@YBZ , Using the department from the first table

a measure like this for project hour

calculate(sum(project[Hour]), allexcept(project, project[Project]))

or

calculate(sum(project[Hour]), filterallselected(project), project[Project] =max( project[Project]) ))

[YBZ]

thanks, I tried your formula but it shows me only the total hours of department A.

It unfortunately doesnt taken into account the other departments that were involved in Factory X.