cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kissmate Regular Visitor
Regular Visitor

Profit&Loss prefixes of the Running Total Amounts

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

5 REPLIES 5
Super User
Super User

Re: Profit&Loss prefixes of the Running Total Amounts

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


kissmate Regular Visitor
Regular Visitor

Re: Profit&Loss prefixes of the Running Total Amounts

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

Super User
Super User

Re: Profit&Loss prefixes of the Running Total Amounts

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Community Support Team

Re: Profit&Loss prefixes of the Running Total Amounts

hi, @kissmate

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kissmate Regular Visitor
Regular Visitor

Re: Profit&Loss prefixes of the Running Total Amounts

Dear @v-lili6-msft,

Képkivágás.PNG

 

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!