Forum Discussion

Alexbienvenue's avatar
Alexbienvenue
Frequent Visitor
4 years ago
Solved

Incorrect total

Hello everyone, having difficulty solving for a correct total, any and all help appreciated

 

 

for context here I need every [SKU] to Display the [juice] in it and display the amount that was "Pressed". The reason I have ALLEXCEPT is that not every [SKU] has an [action] = 'Press" but every [Juice] does. The total amount that is displayed at the bottom is just the sum total of all "press"...not sure why its summing that way 

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    Hi, Alexbienvenue ;

    Try it.

    test = 
SUMX (
    SUMMARIZE (
        'Wine Processing',
        [Juice],
        [Sku],
        "1",
            CALCULATE (
                SUM ( 'Wine Processing'[Amount] ),
                'Wine Processing'[Action] = "Press",
                ALLEXCEPT ( 'Wine Processing', 'Wine Processing'[Juice] )
            )
    ),
    [1]
)

    Or 

    test 2 = SUMX(
SUMMARIZE('Wine Processing',[Sku],[Juice],"1",

CALCULATE (
    SUM ( 'Wine Processing'[Amount] ),
    FILTER (
        ALL ( 'Wine Processing' ),
        'Wine Processing'[Action] = "Press"
    ),
    VALUES ( 'Wine Processing'[Juice] )
)),[1])

    The final show:

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

3 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, Alexbienvenue ;

    Try it.

    test = 
SUMX (
    SUMMARIZE (
        'Wine Processing',
        [Juice],
        [Sku],
        "1",
            CALCULATE (
                SUM ( 'Wine Processing'[Amount] ),
                'Wine Processing'[Action] = "Press",
                ALLEXCEPT ( 'Wine Processing', 'Wine Processing'[Juice] )
            )
    ),
    [1]
)

    Or 

    test 2 = SUMX(
SUMMARIZE('Wine Processing',[Sku],[Juice],"1",

CALCULATE (
    SUM ( 'Wine Processing'[Amount] ),
    FILTER (
        ALL ( 'Wine Processing' ),
        'Wine Processing'[Action] = "Press"
    ),
    VALUES ( 'Wine Processing'[Juice] )
)),[1])

    The final show:

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

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    I am not sure how your filter context is designed on the page, but please try the below.

     

     

    test measure: =
CALCULATE (
    SUM ( 'Wine Processing'[Amount] ),
    FILTER (
        ALLSELECTED ( 'Wine Processing' ),
        'Wine Processing'[Action] = "Press"
    ),
    VALUES ( 'Wine Processing'[Juice] )
)

     

    • Alexbienvenue's avatar
      Alexbienvenue
      Frequent Visitor
      4 years ago

      Hello Jihwan_Kim ,

       

      Thank you for the attmept. Using your DAX this is created:

       

      This is closer but incorrect, playing with the selection filter leads me to believe its excluding two of the rows in that table 

      the 2021_Rose & 2021_Riesling_Dry are not being calculated when they have 2021_riesling_01 as a juice. this is becuase I know there is no Press = "Action" for those two.