Forum Discussion

BAUTNIDE's avatar
BAUTNIDE
Regular Visitor
2 years ago
Solved

Add calculated row values ​​as a total

Hello dear Power BI community,

 

I have the following problem: I have calculated a number of boxes that need to be refilled for a process if the net required quantity is greater than 0. The calculation also works correctly in the table that is output.

 

I also want to add the individual results for each calculated row to a total value. Unfortunately, an incorrect result is sometimes displayed here (see screenshot), because the system in my formula always calculates with the maximum value of the quantity per box, which is different for each row.

 

Does anyone have an idea how I can get the individual intermediate results displayed correctly as a total?

 

Code of the measure:

 

BedarfKLTs = 
IFERROR(
    IF([Nettobedarf Nachschub] / MAX('Zusammenführen1'[tbl_PACKSPEC.Menge]) <= 0, 
        0, 
    ROUNDUP([Nettobedarf Nachschub] / MAX('Zusammenführen1'[tbl_PACKSPEC.Menge]),0)),
0)

 

 

Thanks in advance

Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    2 years ago

    BAUTNIDE , Try updated measure instead of MAX using SUMX

     

    DAX
    BedarfKLTs =
    SUMX(
    'Zusammenführen1',
    IFERROR(
    IF(
    'Zusammenführen1'[Nettobedarf Nachschub] / 'Zusammenführen1'[tbl_PACKSPEC.Menge] <= 0,
    0,
    ROUNDUP('Zusammenführen1'[Nettobedarf Nachschub] / 'Zusammenführen1'[tbl_PACKSPEC.Menge], 0)
    ),
    0
    )
    )

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    BAUTNIDE , Try updated measure instead of MAX using SUMX

     

    DAX
    BedarfKLTs =
    SUMX(
    'Zusammenführen1',
    IFERROR(
    IF(
    'Zusammenführen1'[Nettobedarf Nachschub] / 'Zusammenführen1'[tbl_PACKSPEC.Menge] <= 0,
    0,
    ROUNDUP('Zusammenführen1'[Nettobedarf Nachschub] / 'Zusammenführen1'[tbl_PACKSPEC.Menge], 0)
    ),
    0
    )
    )

  • BAUTNIDE's avatar
    BAUTNIDE
    Regular Visitor
    2 years ago

    Wow, I´m very impressed 😀

    It seems, that it´s working 👍

    Thanks for the very quick reply

  • BAUTNIDE's avatar
    BAUTNIDE
    Regular Visitor
    2 years ago

    Sorry, but it still doesn't work as intended. 😒 I just can't get it to add up correctly. Currently, the formula that comes closest to the result is as follows:

    BedarfKLTs = 
    IFERROR(
        ROUNDUP([Nettobedarf Nachschub] / MAX('Zusammenführen1'[tbl_PACKSPEC.Menge]),0), 
    0)

     

    The following formula is far from the result:

    BedarfKLTB001 = 
    SUMX(
        'Zusammenführen1',
    IFERROR(
        IF(
            DIVIDE([Nettobedarf Nachschub], MAX('Zusammenführen1'[tbl_PACKSPEC.Menge])) <= 0,
                0,
        ROUNDUP(DIVIDE([Nettobedarf Nachschub], MAX('Zusammenführen1'[tbl_PACKSPEC.Menge])), 0)
            ),
                0
            )
    )

     

    Here the current situation:

    The formula behind "Nettobedarf Nachschub" and "BedarfKLTs" are measures and not part of a table.

     

    Any idea, how I get the correct result?