Forum Discussion

BLM's avatar
BLM
Helper I
8 years ago
Solved

Table total error

hello,   i've maked a table with several measures. Specifically the measure "afabricar" don't want that show negatives values (if the result it's negative the valued must be zero). Well, if the me...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi BLM,

     

    Try this formula, please. If it still doesn't work, please share your file or a dummy sample.

    afabricar =
VAR temp =
    IF (
        SUM ( Productos[smincorregido] ) = 0;
        [sminnew];
        SUM ( Productos[smincorregido] )
    )
        - [stockactual]
        + IF ( [totalconsumos] < [sminnew]; [sminnew]; [totalconsumos] )
        - 'ops+stocks+cargas'[opspdts]
RETURN
    IF (
        HASONEVALUE ( 'TableName'[codnombre] );
        IF ( temp < 0; 0; temp );
        SUMX (
            SUMMARIZE (
                'TableName';
                'TableName'[codnombre];
                "temp1"; IF (
                    SUM ( Productos[smincorregido] ) = 0;
                    [sminnew];
                    SUM ( Productos[smincorregido] )
                )
                    - [stockactual]
                    + IF ( [totalconsumos] < [sminnew]; [sminnew]; [totalconsumos] )
                    - 'ops+stocks+cargas'[opspdts]
            );
            [temp1]
        )
    )

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi BLM,

 

The cause is the "Total" has its own context, which doesn't add up the values of the other rows. You try out this formula. If you want a accurate formula, please share your file or a dummy sample.

afabricar =
VAR temp =
    IF (
        SUM ( Productos[smincorregido] ) = 0;
        [sminnew];
        SUM ( Productos[smincorregido] )
    )
        - [stockactual]
        + IF ( [totalconsumos] < [sminnew]; [sminnew]; [totalconsumos] )
        - 'ops+stocks+cargas'[opspdts]
RETURN
    IF (
        HASONEVALUE ( 'TableName'[codnombre] );
        temp;
        SUMX (
            SUMMARIZE (
                'TableName';
                'TableName'[codnombre];
                "temp1"; IF (
                    SUM ( Productos[smincorregido] ) = 0;
                    [sminnew];
                    SUM ( Productos[smincorregido] )
                )
                    - [stockactual]
                    + IF ( [totalconsumos] < [sminnew]; [sminnew]; [totalconsumos] )
                    - 'ops+stocks+cargas'[opspdts]
            );
            [temp1]
        )
    )

Best Regards,

Dale

BLM's avatar
BLM
Helper I
8 years ago

I'm tryed out your formula, and the result it's the same. Show the negatives values and the "Total" it's Ok, but the question is that don't show the negatives values but 0.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi BLM,

     

    Try this formula, please. If it still doesn't work, please share your file or a dummy sample.

    afabricar =
VAR temp =
    IF (
        SUM ( Productos[smincorregido] ) = 0;
        [sminnew];
        SUM ( Productos[smincorregido] )
    )
        - [stockactual]
        + IF ( [totalconsumos] < [sminnew]; [sminnew]; [totalconsumos] )
        - 'ops+stocks+cargas'[opspdts]
RETURN
    IF (
        HASONEVALUE ( 'TableName'[codnombre] );
        IF ( temp < 0; 0; temp );
        SUMX (
            SUMMARIZE (
                'TableName';
                'TableName'[codnombre];
                "temp1"; IF (
                    SUM ( Productos[smincorregido] ) = 0;
                    [sminnew];
                    SUM ( Productos[smincorregido] )
                )
                    - [stockactual]
                    + IF ( [totalconsumos] < [sminnew]; [sminnew]; [totalconsumos] )
                    - 'ops+stocks+cargas'[opspdts]
            );
            [temp1]
        )
    )

    Best Regards,

    Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi BLM,

     

    Could you please mark the proper answer as solution?

     

    Best Regards!
    Dale

    • BLM's avatar
      BLM
      Helper I
      8 years ago
      Thank u so much. It’s the Best forum