Forum Discussion

SebSchoon1's avatar
SebSchoon1
Icon for Post Patron rankPost Patron
3 years ago
Solved

Total is not correct.

Hi Guys,   I have  a problem to understand use of context in DAX   here is my model     Where i have only one dim table (Base Article) and one fact Table (Tableau Dispatch)   Where i w...
Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    3 years ago

    Hi SebSchoon1 ,

     

    I can't open the link you provided for now, but for this type of problem, it should be a problem caused by the external filtering context in the created formula. Refer to the formula comparison in the following test.

    Amount_filter = sum(FactInternetSales[SalesAmount])
    Result1 =
VAR div2 =
    CALCULATE ( [Amount_filter], ALL ( DimProduct ) )
RETURN
    DIVIDE ( [Amount_filter], div2, BLANK () )

Result2 =
VAR div2 =
    CALCULATE ( [Amount_filter], ALLEXCEPT ( DimProduct, DimProduct[_ABC Class] ) )
RETURN
    DIVIDE ( [Amount_filter], div2, BLANK () )

     

    More details, you can read below blog:

    Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI

     

    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


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

     

v-henryk-mstf's avatar
v-henryk-mstf
Icon for Community Support rankCommunity Support
3 years ago

Hi SebSchoon1 ,

 

There should be an error in the total value caused by the external context filter condition. Can you provide a screenshot of the expected results and the test data with a description, so that I can answer you as soon as possible.

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

 

In the meantime, you can read the below blog about the behavior of the ALLxxx functions in DAX.

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI


Looking forward to your reply.


Best Regards,
Henry

 

  • SebSchoon1's avatar
    SebSchoon1
    Icon for Post Patron rankPost Patron
    3 years ago

    Hi v-henryk-mstf 

     

    Sorry for the late reply,

     

    Here is what i expect

     

    CODE ARTICLECODE DEPOTTAILLERANK1-Total articles en stockTotal DépotsTotal quantités tous dépotsRépartitionPartie entièrePartie décimaleReliquat totalRépartition finale
    DIF500ASP015 13351,6710,6722
    DIF500ASP010 22351,6710,6722
    DIF500ASP040 30351,6710,6721
    LODSTICKS70WH015 16382,6720,6723
    LODSTICKS70WH010 22382,6720,6723
    LODSTICKS70WH040 30382,6720,6722
        133131394413

     

    Here is what i Get for now 

    I'm not able to manage calculations on different levels in Dax, it is quite frustrating..

     

    Here is a link with sample

     

    https://we.tl/t-KwOgHOZDJF

     

     

    Thanks in advance 

     

     

     

    • v-henryk-mstf's avatar
      v-henryk-mstf
      Icon for Community Support rankCommunity Support
      3 years ago

      Hi SebSchoon1 ,

       

      I can't open the link you provided for now, but for this type of problem, it should be a problem caused by the external filtering context in the created formula. Refer to the formula comparison in the following test.

      Amount_filter = sum(FactInternetSales[SalesAmount])
      Result1 =
VAR div2 =
    CALCULATE ( [Amount_filter], ALL ( DimProduct ) )
RETURN
    DIVIDE ( [Amount_filter], div2, BLANK () )

Result2 =
VAR div2 =
    CALCULATE ( [Amount_filter], ALLEXCEPT ( DimProduct, DimProduct[_ABC Class] ) )
RETURN
    DIVIDE ( [Amount_filter], div2, BLANK () )

       

      More details, you can read below blog:

      Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT - SQLBI

       

      If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


      Best Regards,
      Henry


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