Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
    Anonymous
    Not applicable

    Hi Anonymous ,

     

    Could you provide some dummy data?


    Best Regards,
    Eyelyn Qin

    • Anonymous's avatar
      Anonymous
      Not applicable

      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's avatar
        Anonymous
        Not applicable

        Hi Anonymous 

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

         

        Regards,

         

        Ahad