Forum Discussion
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
- AnonymousNot 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 CommunityHow 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. - AnonymousNot 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?
- AnonymousNot applicable
Hi Anonymous ,
Could you provide some dummy data?
Best Regards,
Eyelyn Qin- AnonymousNot 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- AnonymousNot applicable
Hi Anonymous
I just wanted to ask if you got a chance to work on this?Regards,
Ahad