Forum Discussion

Rich_Wyeth's avatar
Rich_Wyeth
Helper I
1 year ago
Solved

Calculation With Filters

Hi All,   I am struggling to get something to work, despite looking at examples, I can't get this to work.   M_THECALC = CALCULATE(SUM('Margin Report'[QTY])+SUM('KITS 2024'[Quantity]))   This ...
Need Help
  • v-achippa's avatar
    v-achippa
    1 year ago

    Hi Rich_Wyeth,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Due to the many-to-many relationships and use of bridge tables like KITS JOIN, the slicer does not automatically filter KITS 2024. This is why you are seeing overall totals instead of values specific to the selected part.

    Use this below measure, it will correctly respects the slicer selection and filters the kit quantities accordingly:

     

    M_THECALC =

    VAR SelectedParts = VALUES('Margin Report'[PN])

     

    VAR DirectQty =

        CALCULATE(

            SUM('Margin Report'[QTY]),

            KEEPFILTERS(SelectedParts)

        )

     

    VAR KitsQty =

        CALCULATE(

            SUM('KITS 2024'[Quantity]),

            TREATAS(SelectedParts, 'KITS 2024'[Part Number])

        )

     

    RETURN

    DirectQty + KitsQty

     

    And also ensure 'KITS 2024'[Part Number] and 'Margin Report'[PN] have the same data type and check if all the relevant relationships in the model are active.

     

    If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

     

    Thanks and regards,

    Anjan Kumar Chippa

v-achippa's avatar
v-achippa
Community Support
1 year ago

Hi Rich_Wyeth,

 

Thank you for reaching out to Microsoft Fabric Community.

 

Due to the many-to-many relationships and use of bridge tables like KITS JOIN, the slicer does not automatically filter KITS 2024. This is why you are seeing overall totals instead of values specific to the selected part.

Use this below measure, it will correctly respects the slicer selection and filters the kit quantities accordingly:

 

M_THECALC =

VAR SelectedParts = VALUES('Margin Report'[PN])

 

VAR DirectQty =

    CALCULATE(

        SUM('Margin Report'[QTY]),

        KEEPFILTERS(SelectedParts)

    )

 

VAR KitsQty =

    CALCULATE(

        SUM('KITS 2024'[Quantity]),

        TREATAS(SelectedParts, 'KITS 2024'[Part Number])

    )

 

RETURN

DirectQty + KitsQty

 

And also ensure 'KITS 2024'[Part Number] and 'Margin Report'[PN] have the same data type and check if all the relevant relationships in the model are active.

 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

 

Thanks and regards,

Anjan Kumar Chippa

v-achippa's avatar
v-achippa
Community Support
1 year ago

Hi Rich_Wyeth,

 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

 

Thanks and regards,

Anjan Kumar Chippa

  • v-achippa's avatar
    v-achippa
    Community Support
    1 year ago

    Hi @Rich_Wyeth,

     

    We wanted to kindly follow up to check if the solution I have provided for the issue worked.
    If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

     

    Thanks and regards,

    Anjan Kumar Chippa

  • v-achippa's avatar
    v-achippa
    Community Support
    1 year ago

    Hi @Rich_Wyeth,

     

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
    If my response addressed, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

     

    Thanks and regards,

    Anjan Kumar Chippa