Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
slyfox
Helper II
Helper II

wrong column sum for allexcept measure

Dear Community,

 

Please help to solve an issue with Column SUM for ALLEXCEPT related measure.

Report return expected row results, but sum for this row definitely wrong.

 

PBI file https://send.firefox.com/download/eb46fe9596ae65ef/#UdUFN3Z6BdOjr3PJIym-Ew
or https://www.plustransfer.com/download.php?id=63432b87a5dd29dd59358ee445c93619&email=%C2%89%7Ftw%7C%C...

 

 

Comm 1.png

1 ACCEPTED SOLUTION
v-cherch-msft
Employee
Employee

Hi @slyfox 

I would suggest you create a measure to get the correct total.

Measure =
SUMX (
    SUMMARIZE (
        F_Shipment_Delivery,
        F_Shipment[SMP_Id],
        F_Shipment_Delivery[LINK_Delivery_Order]
    ),
    [Distr_NegoCost]
)

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
v-cherch-msft
Employee
Employee

Hi @slyfox 

I would suggest you create a measure to get the correct total.

Measure =
SUMX (
    SUMMARIZE (
        F_Shipment_Delivery,
        F_Shipment[SMP_Id],
        F_Shipment_Delivery[LINK_Delivery_Order]
    ),
    [Distr_NegoCost]
)

1.png

Regards,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
vanessafvg
Super User
Super User

@slyfox  hi ok so something is wrong, but what are you expecting, can you give me something to compare it to?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Hi @vanessafvg 
Result for this column should be the same as for SM_Negotiated Cost

@slyfox  ok then the problem is your relationships.  you have all your relationships set up to bi directional, you should change that to single and that should resolve your issue





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




I was having the same problem and your suggestion solved it! THANK YOUUUU! @vanessafvg 

@vanessafvg  

I did not find the answer in your posts. Links are set in the form in which it was required.

If you do not know how to solve the problem with the defined conditions, please ignore the post.

@vanessafvg Suggest workflow cannot be applied because it brings to wrong results in row by row calculations.

https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@vanessafvg 
In my example, only 1/20 of the tables used in the model. My question is simple, within the existing data model it is possible to solve the problem or not?

yes as  i have said, disabling some of the the bidirectional relationships does this, however this is not as simple as you making out,  the onus is on you to understand how power bi works if this does not meet all your requirements.   what i am saying is right now based on your relationship model, this is is what is going to be returned, its important to undertand the behaviour of bi directional relationshipscapture1.PNGships. 

 

Capture.PNG

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@slyfox you mean relationships flow?   then you might need to think about remodeling your data because that is what the relationships set up in both directions do.   





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors