Forum Discussion

MarlenePirker's avatar
MarlenePirker
Helper I
3 years ago

Incorrect Total for NON-DISTINCT values

Hello, 

 

I am creating a visual looking like that: 

I am showing the delivery in comparison to the order. The yellow column is a measure. 

SKU Del. QTY cut = IFERROR(
    IF(SUM(Fact1_Delivered[Total Del. QTY])>SUM(Fact2_Allocation[Allocation QTY]), SUM(Fact2_Allocation[Allocation QTY]),SUM(Fact1_Delivered[Total Del. QTY])),
    BLANK()
    )
 
The total value from SKU QTY is not calculated correctly. I know this issue and I had this before. I always solved this, using another measure like that: 
SKU QTY = IF(HASONEVALUE('Article Bridge'[Article Number]),[SKU Del. QTY cut], SUMX(Values('Article Bridge'[Article Number]), [SKU Del. QTY cut]))
 
The problem is that this measure apparently ONLY works for unique values but as you can see the articles numbers can appear more than once in different weeks - so it doesnt work. 
 
Can someone help me how to solve this? 
 
Thank you! 
 
best regards, 
Marlene

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi MarlenePirker ,

     

    Please try this measure:

    Measure = 
SUMX(    
SUMMARIZE(   
'Article Bridge',   
'Article Bridge'[Article Number]   ,         
"total",[SKU Del. QTY cut]    ),    
[total])

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

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

    • MarlenePirker's avatar
      MarlenePirker
      Helper I
      3 years ago

      Unfortunately this doesnt work... For the SKU QTY I get 40.204 but it should be 10700. When I filter for only one Week and look at the articles (cause they are unique now) it works. But as soon as I have my overview containing all weeks (2023.01-2023.09) and the articles are not unique anymore the total value is not correct anymore.. I dont really have time to create a new small dataset, I hope that the issue is clear.. 

      Thank you so much for your reply!

      Best regards, 

      Marlene