How to create a dax calculation which doesnt change after dimension filter

[Anonymous]

Hi Everyone, 

I have a little pickle. I have this calculation i use to calculation a fee i have to pay a provider. this fee is based on the min max amount of a total number. The number works out fine. Here is the calculation;

Amount_Staffel_PAO = 
VAR CorrectStaffel =
    FILTER (
        Dim_Staffel_PAO,
        Dim_Staffel_PAO[MIN] < [Amount_Segment_PAO]
            && Dim_Staffel_PAO[MAX] >= [Amount_Segment_PAO]
    )
RETURN
    CALCULATE (
        SELECTEDVALUE (Dim_Staffel_PAO[Fixed Comp.] )
            + SELECTEDVALUE ( Dim_Staffel_PAO[Price] )
                * ( [Amount_Segment_PAO] - SELECTEDVALUE ( Dim_Staffel_PAO[MIN] ) ),
        CorrectStaffel
    
    )

The issue is when i try to slice the result of this dax calculation into our suppliers. the number is for example 500 usd. but when i slice it to show me the split of this 500 on supplier level. the calculation changes it to 350 for one and 250 for the other supplier. I believe this happens because dax first splits the data into the dimension then checks the max min amounts for the fee and that would be higher because low amount = higher fee. 

So my question, can i get the total number i have to pay and then split it on the supplier within this dax calculation?

Regards,

Ahad

[Anonymous]

Hi @Anonymous ,

Could you provide some dummy data?

Best Regards,
Eyelyn Qin

[Anonymous]

Hi Evelyn,

Thank you so much for still sticking with me. I dummified the dataset and made a pbi report from it.

Please find the excel datasheet and the pbi workbook. i already made the graphs side by side showing their difference in numbers.

Let me know if you need anything else!

https://1drv.ms/u/s!AkgNNBg256JegaQyXS-EVsr0xsZZPg?e=5pzsib

[Anonymous]

Hi @Anonymous 

I just wanted to ask if you got a chance to work on this? 

Regards,

Ahad

[Anonymous]

Hi Evelyn, i tried using all but that doesnt help plis find the three scenarios here, the original amount:

the amount with supplier split,

and if i add all(supplier) in the dax code i get this;

why does this happen?

[Anonymous]

Hi @Anonymous ,

Sorry for that the information you have provided is not making the problem clear to me.

but when i slice it to show me the split of this 500 on supplier level. the calculation changes it to 350 for one and 250 for the other supplier.

So all supplier should be 500?  If so, you may use add ALL('Table'[Supplier]) to the filters. 

If not, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:
How to Get Your Question Answered Quickly - Microsoft Power BI Community

How to provide sample data in the Power BI Forum - Microsoft Power BI Community

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