Forum Discussion

Peter_2020's avatar
Peter_2020
Helper III
3 years ago
Solved

Sum with specific filter

Hi all, 

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

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:

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. 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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.

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

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

     

    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] )
        )
    )
)

     

     

     

    • Peter_2020's avatar
      Peter_2020
      Helper III
      3 years ago

      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:

       

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        3 years ago

        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.