Calculation With Filters

[Rich_Wyeth]

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 is the calculation I have, but at this stage it is calculating totals, what I need it to do is calculate based on the slicer filter I have on the page. This slicer is coming from my 'Margin Report[PN] field.

What I did try was:

M_THECALC = CALCULATE(SUM('Margin Report'[QTY])+SUM('KITS 2024'[Quantity]),FILTER(ALL('Margin Report'[PN]),SELECTEDVALUE('Margin Report'[PN])))

This fails, so clearly not correct.

Can anyone help me out, where am I going wrong?

[Deku]

Is there a relationship between kits and margin?

[Rich_Wyeth]

Yes, they are joined by Part Number [PN}

[Deku]

What type of relationship? Margin 1:many kits?

[Rich_Wyeth]

It is Many to Many. 

The Margin Table holds a column for Part Number, some of these part numbers are Kit numbers. So I have a Kits Join Table (Kit Number & Part Number) and a Kit Numbers table (Kit Number & Part Number). The Join table links to the Margin Report via [PN] and the Kits Numbers table links to Kits Join via the Kit Number. This is the only way I have been able to get the sums to work correctly from each table. I now want a total number of the two, which I am struggling to achieve. Joins

[Rich_Wyeth]

The scenario is a bit convaluted.

The Margin Table has one colunm [PN] that includes both Part Number and Kit Number. The Kits Tables have them separated between Kit and Part. There can be multiple parts to a kit and the part can appear in more than one kit. Each part number has a specific qty to being in the kit. I have managed to get a table to show me the results of total qty for parts in kits (From Kits) and Ihave a tabe for just parts sold separately (from Margin Report). I am trying to get the two totals to now caclulate up, when I have selected the part number from my slicer (coming from Margin Report). What currently happens is ut sums total quatities rather than specific to the part number selected.

[v-achippa · Accepted solution]

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]

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]

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]

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