Forum Discussion

Kjellke's avatar
Kjellke
Helper I
4 years ago

Wrong summarized values visual table

Hi, i am quite new to Power BI, but i am trying to make some with data ive successfully exported from our ERP system.

I am now trying to have two collumns that one that summarize the positive numbers, and one that summarize the negative numbers, and show the total number on the bottom of my visual table.

 

I am using the following formulas.

 

 

FORMULA = 
VAR _profit = CALCULATE(SUMX(PROJTRANSPOSTING,PROJTRANSPOSTING[AMOUNTMST]),
PROJTRANSPOSTING[ACCOUNT]="2901",
PROJTRANSPOSTING[DATAAREAID]="100",
PROJTRANSPOSTING[CATEGORYID]="Profit")

VAR _kost = CALCULATE(SUMX(PROJTRANSPOSTING,PROJTRANSPOSTING[AMOUNTMST]),
PROJTRANSPOSTING[POSTINGTYPE]<>122,
PROJTRANSPOSTING[COSTSALES]=1,
PROJTRANSPOSTING[AMOUNTMST]<>.000000000000)

VAR _fakturert = SUMX(PROJINVOICEJOUR,PROJINVOICEJOUR[FAKTURERT])

return IF(_fakturert-_kost-_profit>0,_fakturert-_kost-_profit,0)
FORMULA1 = 
VAR _profit = CALCULATE(SUMX(PROJTRANSPOSTING,PROJTRANSPOSTING[AMOUNTMST]),
PROJTRANSPOSTING[ACCOUNT]="2901",
PROJTRANSPOSTING[DATAAREAID]="100",
PROJTRANSPOSTING[CATEGORYID]="Profit")

VAR _kost = CALCULATE(SUMX(PROJTRANSPOSTING,PROJTRANSPOSTING[AMOUNTMST]),
PROJTRANSPOSTING[POSTINGTYPE]<>122,
PROJTRANSPOSTING[COSTSALES]=1,
PROJTRANSPOSTING[AMOUNTMST]<>.000000000000)

VAR _fakturert = SUMX(PROJINVOICEJOUR,PROJINVOICEJOUR[FAKTURERT])

return IF(_fakturert-_kost-_profit<0,_fakturert-_kost-_profit,0)

 

 

 

 


The individual lines seems to show the correct numbers, but my totals are completely off, and is even missing on Collumn for FORMULA1. (See printscreen).

I was wondering if anyone has any tips that could please point me in the right direction for this.

I would highly appreciate any help on this subject.

Need Help

15 Replies

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

    Hi, Kjellke 

     

    Because your final total in formula1 is negative, it returns to zero according to your principle.

    You can use summarize() function to modify the total.

    Like this:

    FORMULA1 =
SUMX (
    SUMMARIZE (
        PROJTRANSPOSTING,
        [ACCOUNT],
        [DATAAREAID],
        [CATEGORYID],
        [AMOUNTMST],
        [FAKTURERT],
        "a",
            VAR _profit =
                CALCULATE (
                    SUMX ( PROJTRANSPOSTING, PROJTRANSPOSTING[AMOUNTMST] ),
                    PROJTRANSPOSTING[ACCOUNT] = "2901",
                    PROJTRANSPOSTING[DATAAREAID] = "100",
                    PROJTRANSPOSTING[CATEGORYID] = "Profit"
                )
            VAR _kost =
                CALCULATE (
                    SUMX ( PROJTRANSPOSTING, PROJTRANSPOSTING[AMOUNTMST] ),
                    PROJTRANSPOSTING[POSTINGTYPE] <> 122,
                    PROJTRANSPOSTING[COSTSALES] = 1,
                    PROJTRANSPOSTING[AMOUNTMST] <> .000000000000
                )
            VAR _fakturert =
                SUMX ( PROJINVOICEJOUR, PROJINVOICEJOUR[FAKTURERT] )
            RETURN
                IF ( _fakturert - _kost - _profit < 0, _fakturert - _kost - _profit, 0 )
    ),
    [a]
)

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please upload some insensitive data samples and expected output.


    Best Regards,

    Community Support Team _ Janey

    • Kjellke's avatar
      Kjellke
      Helper I
      4 years ago

      Thank you for your answer Team_Janey. This is the closest ive ever been. The individual values are now wrong, but the subtotals seems to be calculated (wrong values).

      The Summarize function above does not seem to find the [Fakturert] values because that is from table: PROJINVOICEJOUR and not PROJTRANSPOSTING.

      Do you have any suggestions on how to get thoose numbers included?

      Thank you again.

    • Kjellke's avatar
      Kjellke
      Helper I
      4 years ago

      I forgot to mention that my objective is to get all the negative values in one collumn, and all the positive values in one collumn.

      So basically i want to do the calculations for every row (every project), and if the sum is >0, show it in one collumn and if it is <0, show it in another.

      With correct grand totals at the bottom

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

        Hello Kjellke 

         

        Are these two tables related? If yes, you can try:

        FORMULA1 =
SUMX (
    SUMMARIZE (
        PROJTRANSPOSTING,
        [ACCOUNT],
        [DATAAREAID],
        [CATEGORYID],
        [AMOUNTMST],
        "a",
            VAR _profit =
                CALCULATE (
                    SUMX ( PROJTRANSPOSTING, PROJTRANSPOSTING[AMOUNTMST] ),
                    PROJTRANSPOSTING[ACCOUNT] = "2901",
                    PROJTRANSPOSTING[DATAAREAID] = "100",
                    PROJTRANSPOSTING[CATEGORYID] = "Profit"
                )
            VAR _kost =
                CALCULATE (
                    SUMX ( PROJTRANSPOSTING, PROJTRANSPOSTING[AMOUNTMST] ),
                    PROJTRANSPOSTING[POSTINGTYPE] <> 122,
                    PROJTRANSPOSTING[COSTSALES] = 1,
                    PROJTRANSPOSTING[AMOUNTMST] <> .000000000000
                )
            VAR _fakturert =
                SUMX ( RELATEDTABLE ( PROJINVOICEJOUR ), PROJINVOICEJOUR[FAKTURERT] )
            RETURN
                IF ( _fakturert - _kost - _profit < 0, _fakturert - _kost - _profit, 0 )
    ),
    [a]
)

         

         

        Best Regards,

        Community Support Team _ Janey