Reply
Peter_2020
Helper III
Helper III
Partially syndicated - Outbound

Sum with specific filter

Hi all, 

I would like to ask you for help with following situation. I have this kind of data:

Peter_2020_1-1671514043897.png

And I need to activate filter for code = 20 and see how many hours was reported for each operation. But when I activate this filter it looks like this:

Peter_2020_2-1671514207551.png

So I need to get somehow the hours from specific PO and specific OPERATION and CODE 01 to CODE 20. It will looks like this:

PO - OPER - CODE - HOURS - QUANTITY

PO - 0901 - 20 - 0,67 - 1,00

PO - 0902 - 20 - 0,69 - 1,00

PO - 0903 - 20 - 0,59 - 1,00

PO - 0904 - 20 - 0,83 - 1,00

PO - 0905 - 20 - 1,42 - 1,00

PO - 0990 - 20 - 1,00 - 1,00

 

Thank you in advance. 

P. 

1 ACCEPTED SOLUTION

Syndicated - Outbound

Hi,

I cannot check whether my measure is working as expected without seeing a different PO number in the data.

Please check the attached pbix file, that I have fixed a little bit. Or, provide your sample pbix file's link in order to see the correct structure of your data.

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

4 REPLIES 4
Peter_2020
Helper III
Helper III

Syndicated - Outbound

@Jihwan_Kim After your correction it works. Thank you very much for your help!

Jihwan_Kim
Super User
Super User

Syndicated - Outbound

Hi,

I assume the logic is getting accumulate total in each operation.

Jihwan_Kim_0-1671516143248.png

 

Expected measure hours: =
IF (
    COUNTROWS ( ALLSELECTED ( Data[Code] ) ) = COUNTROWS ( ALL ( Data[Code] ) ),
    SUM ( Data[Hours] ),
    CALCULATE (
        SUM ( Data[Hours] ),
        WINDOW (
            1,
            ABS,
            0,
            REL,
            ALL ( Data[PO], Data[Operation], Data[Code] ),
            ORDERBY ( Data[Code], ASC ),
            KEEP,
            PARTITIONBY ( Data[Operation] )
        )
    )
)

 

 

Expected measure quantity: = 
IF (
    COUNTROWS ( ALLSELECTED ( Data[Code] ) ) = COUNTROWS ( ALL ( Data[Code] ) ),
    SUM ( Data[Quantity] ),
    CALCULATE (
        SUM ( Data[Quantity] ),
        WINDOW (
            1,
            ABS,
            0,
            REL,
            ALL ( Data[PO], Data[Operation], Data[Code] ),
            ORDERBY ( Data[Code], ASC ),
            KEEP,
            PARTITIONBY ( Data[Operation] )
        )
    )
)

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Syndicated - Outbound

Hi @Jihwan_Kim , 

thank you for your help. I forgot to mention that in the database is more than 1 PO so it needs to be filtered also by the PO. That´s the probably reason why I get this output:

Peter_2020_0-1671518552322.png

 

 

Syndicated - Outbound

Hi,

I cannot check whether my measure is working as expected without seeing a different PO number in the data.

Please check the attached pbix file, that I have fixed a little bit. Or, provide your sample pbix file's link in order to see the correct structure of your data.

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)