Profit&Loss prefixes of the Running Total Amounts

[Anonymous]

Dear All,

I created a P&L report, but I have little problems with prefixes. I have a table like this one:

1. Main Group 1.
   • Second Level Group 1.: 100
   • Second Level Group 2.: -20
   • Running Total: 80
2. Main Group 2.: Total
   • Second Level Group 3.: -50
   • Second Level Group 4.: 20
   • Running Total: 50

I have a running total amount column by the main groups as you seen, 1. Main Group is 80 and 2. Main Group total is 80 -50 +20 = 50.

The only thing I want to change is that if the amount is negative (these are the costs), I'd like to see these amount by positive prefix, but in the Running Total I'd like to see the calculation like in the example,:

Main Group 1.

• Second Level Group 1.: 100
• Second Level Group 2.: 20 (this is the cost but I'd like to see as posotive, but in the calculation its negative because 100-20 = 80)
• Running Total: 80

Do you have any idea how can I solve this problem?

Thank you in advance for your help!
Mate

[Greg_Deckler]

Not sure I'm 100% clear on this but you should be able to create your measure such that you return 20 instead of -20 by using a simple SWITCH or IF statement.

[Anonymous]

Dear @Greg_Deckler

Thanks for your reply!
I know I can change the numbers to positive, but than in my example, in the calculation maybe in the 1. Main Group it will be 120 instead of 80, because there will be 100 & 20 (20 is the cost but positive) but in the measure itt will add to the 100 won't it? Thats my huge question! I want that 100 - 20 = 80, but as the table shows +20 instead of -20 -> (+)100 & (+)20 (but this is the cost)= 80

Any idea?
Thank you very much!!

Mate

[v-lili6-msft]

hi, @Anonymous

Could you please share your sample pbix file, We need specific data structures, You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[Anonymous]

Dear @v-lili6-msft,

I've this table with the measure:

Sales (selected month) running total =

CALCULATE(

    [Sales (selected month)];

    FILTER(

        CALCULATETABLE(

            SUMMARIZE(

                'GLEntry';

                'GLEntry'[New Main Group No.];

                GLEntry[New Main Group]

            );

            ALLSELECTED('GLEntry')

        );

        ISONORAFTER(

            'GLEntry'[New Main Group No.]; MAX('GLEntry'[New Main Group No.]); DESC;

            [New Main Group]; MAX([New Main Group]); DESC

        )

    )

)

In this running total I'd like to see everíthing with + prefix, also when it's a cost (where now is -), but the running totals should be the same, so it should be sales - costs, but cost also with + prefix.

Thank you in advance!

[Greg_Deckler]

I was thinking that you would create a second measure for display purposes. So, use your original measure in your running total but create another measure for display purposes that returns positive instead of negative. That was the idea. But, not sure if it will work as I am not 100% on your setup.