Wrong summarized values visual table

[Kjellke]

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.

[v-janeyg-msft]

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]

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]

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

[Kjellke]

Thank you again for your feedback. The tables are related, PROJTRANSPOSTING filters PROJINVOICEJOUR by projectnumber.

I now get the following numbers:

My goal is to get the VIA collumn (but separated, so when the sum is a negative number, i get it in one collumn, and when the sum is a positive number in another collumn). 

[v-janeyg-msft]

@Kjellke 

So does the code I provided work? What else is wrong?

I don’t know how your via column is calculated. If you have new requirements, Can you share a sample data file and your calculatoon logic and your desired result, so we can help you soon.

Best Regards,

Community Support Team _ Janey

[Kjellke]

Dear @v-janeyg-msft, i do not know how to export sample data. Do you know if there is a guide for this?

[v-janeyg-msft]

@Kjellke 

Check it:How to provide sample data in the Power BI Forum - Microsoft Power BI Community

[Kjellke]

Please see if you can find the sample data on the following link.

https://docs.google.com/spreadsheets/d/1HYcAnWjSu541kDaEmJZ9MLEZrlO6CUwmbOl_cczz9Ts/edit?usp=sharing

[Kjellke]

@v-janeyg-msft did this link work for you? 🙂

[v-janeyg-msft]

Hi, @Kjellke 

First of all, I can say that your needs are really not hard to achieve. But there are many contradictions between your sample and description, I hope you can explain it. Otherwise I can't test.

Without context, measure has only one value. How can it be calculated based on each row? You only mentioned the two columns you want, so what are the other columns in the visual?

In order to finally get the correct total, you may need write the code in the form of sumx(summarize()),so you only need to explain your calculation logic clearly (the text form is not the code you wrote), then I will help you modify it .

The sample data doesn't need to be much, it only needs to be representative, including the columns used for calculation.

I hope you can understand what I said and make changes so that I can help you soon.

Best Regards,

Community Support Team _ Janey

[Kjellke]

Dear @v-janeyg-msft , i am sorry for the late response.

I have tried removing the filter on projtransposting[amountmst] as i could not remember why i had it in the first place.

The projtransposting[dataareaid]="100" is just a filter to use ERP data from the company that i am making the report for, as our ERP system has dummy data and other company codes. (Not really relevant for the calculations).

Projtransposting[Categoryid]="profit" is that the table is a transaction postlog where costs, revenues and profits are differentiated with the categoryid. I only want to extract values with the categoryid Profit.

Hope this explains more

[Kjellke]

Dear Janeyg,

The code does not provide me with the correct numbers. The VIA collumn is just the same as the other collumns but without the IF function.

Do you know how i can export samle data?

Please see below for the VIA code

VIA = 
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 _fakturert-_kost-_profit

[v-janeyg-msft]

Hi, @Kjellke 

If the via column is the answer you want, so your first two columns (in your picture)have more than total problems, but are all the values wrong?

I need your sample source data, measure result doesn't important for me.

[Kjellke]

@v-janeyg-msft 

I tried to make an example in Excel as it may be easier to explain.
Please see if this helps to understand what i am trying to achieve,  i am sorry for the poor descriptions.

[Kjellke]

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.