Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
WestMart
Helper I
Helper I

How to make a counter-sign only to the liability and net in the Balance Sheet.

Hello friends:

I have the following problem that I cannot solve: In a balance sheet the asset must add the same as liabilities and equity.

And in the matrix that I have made with the result, but I wanted the liability and equity to have the opposite sign to the one they have in the matrix, since it is as usual these types of financial statements are presented.

I attached a link to the sample .pbix file that I made so that the problem can be better seen.

In this example, the capital should have a positive sign and the profit after tax should have a negative sign, because the result is a loss. Therefore, the ABS function is not useful to me, because it converts all values to positive.

On the other hand, since the data that appears are up to the month before the current one, I wanted the values corresponding to the last month to be displayed in the total, in this case it would be October. How could I get this?

link to .pbix file: https://1drv.ms/u/s!AgSW1jpnrBq_gqxK8MWVpu-5bOtN8A?e=JOegGn

It would grace the help.

Thank you very much in advance.

Sincerely, Agustín Martínez.

1 ACCEPTED SOLUTION

@WestMart

Sorry, it's just that seeing this image looked like the "Profit After Tax" line you wanted it positive:

true.jpg

If all lines under "Passive" must change signs, use this measure:

Importe Balance para matriz = 
VAR Pasivo = CALCULATETABLE(VALUES(BalanceResumido[Nivel I]), 
                FILTER(BalanceResumido, 
                    BalanceResumido[Nivel III] = "Pasivo"))
RETURN
           IF(COUNTROWS(Pasivo) >=1, 
                [Importe Balance Acumulado Hasta Hoy] *-1, 
                    [Importe Balance Acumulado Hasta Hoy])

final.JPG





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

8 REPLIES 8
mahoneypat
Employee
Employee

For the first question, you could just multiple both measures by -1 to get the opposite sign.  For the second, you can use this measure expression which references your existing measure but returns the result for the last month in context that isn't blank.  In your example, the last month is Oct2020, so that will show in the total.  There is probably a more efficient way to get there but this works.

 

NewMeasure =
VAR lastmonth =
    CALCULATE (
        MAX ( Calendario[OrdenMes] ),
        FILTER (
            ALL ( Calendario[OrdenMes] ),
            NOT (
                ISBLANK ( [Importe Balance Acumulado Hasta Hoy] )
            )
        )
    )
RETURN
    CALCULATE (
        [Importe Balance Acumulado Hasta Hoy],
        Calendario[OrdenMes] = lastmonth
    )

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Thank you very much Mahoneypat for the response and help.

The measure you are proposing to me to show the amount of the last month is just what I was looking for and it helps me to finish the last column of the display matrix. Fantastic!

But with regard to the proposal to multiply by -1 for the change of sign in the liability and net does not solve the problem for me, because if that measure multiplies it by -1 I also change the sign of the asset, and it is not correct.

You would need the asset to remain positive, and for the liability and net to change the sign that appears in the current display matrix. If the result I obtain with the current measure is negative, as in the case of some liability accounts, it should be positively signed, and if the result appears positive, as is the case with the Benefit, it should be negatively signed

The attached image shows the results that are correct (Ok) and those that should have the opposite sign (aspa)

Thank you very much for your attention and help.

Greetings.

Westmart

pantallazo.jpg

@WestMart 

As regards changing the format for the visual, you can do this in the model view. Select the measure and go to properties. You can set the format for both the positive and negative values (apologies since the following images are from a different example, but I hope it provides the relevant pointers):

3 modeling pane.JPG

Format Custom explan.png

 

So if you want a positive value to be displayed as a negative, add a "-" symbol in the string for the positive expression; if you want a negative value to be displayed as positive, remove any negative symbol in the string for the negative expression. 
BTW, this method does not change the actual value; it only changes the display format.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hello PaulDBrown:

Thank you very much for the help, but it is not useful to me because changing the format of the visual assumes that it would change all positive values of the array to negative, and vice versa.

In my case, some of the positive values are correct, those that are part of Level III "Active" and I want them to be maintained in this format.

I want to change sign those values that are part of Level III "Passive". And the negative values that are part of "Passive" would be displayed as negative and the negative values would be displayed as positive.

Thanks a lot.

Kind regards

Westmart.

@WestMart 

Is this what you are looking for?

matriz.JPG

 If so, try this measure:

Importe Balance para matriz = 
VAR Pasivo = CALCULATETABLE(VALUES(BalanceResumido[Nivel I]), 
                FILTER(BalanceResumido, 
                    BalanceResumido[Nivel III] = "Pasivo"))
RETURN
IF(
    SELECTEDVALUE(BalanceResumido[Nivel I]) = "Beneficio después de impuestos", 
        [Importe Balance Acumulado Hasta Hoy],
            IF(COUNTROWS(Pasivo) >=1, 
                [Importe Balance Acumulado Hasta Hoy] *-1, 
                    [Importe Balance Acumulado Hasta Hoy]))




Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hello PaulDBrown:

Thank you very much for the quick answer, but I am sorry to tell you that the result you get from the proposed measure is not the one I intend to obtain.

In the image attached I include three tables:

1.- the orginal result of the matrix, with the measure I am using, and that is not correct:

Amount Balance Accumulated Up to Today
IF(LASTDATE(Calendar[Calendar] ) > TODAY();
BLANK();
CALCULATE([Balance Amount];D ATESYTD(Calendar[Calendar])))

2- The second picture box appears applying your proposed measure: "Matrix Balance Amount"

Balance Amount for Matrix . . . . . . . . . . . . .
Passive VAR - CALCULATETABLE(VALUES(BalanceResumed[Level I]);
FILTER(BalanceResumido;
Summary Balance[Level III] - "Passive"))
RETURN
IF(
SELECTEDVALUE(BalanceResumed[Level I]) - "Benefit after tax";
[Amount Balance Accumulated To Today];
IF(COUNTROWS(Passive) >-1;
[Amount Balance Accumulated To Today] *-1;
[Accumulated Balance Amount To Today]))

3.- And in the third box of the image appears the result that we want to obtain:

All values belonging to Level III "Active" are correct. And the values that belong to "Passive" must have the opposite sign to the one they have in the first box.

That is, in this particular case, "Other creditors", "Suppliers" and "Benefit after tax" must be in an amount contrary to the original measure, i.e. they have to have a negative sign.

matrices balance de situacion.jpg

I hope that you will be clearer about the result that I want to achieve in this way to draw up the necessary measure.

I am very grateful for your decision, collaboration and help in the problem I have raised.

Thanks a lot

A cordial greeting.

Westmart.

@WestMart

Sorry, it's just that seeing this image looked like the "Profit After Tax" line you wanted it positive:

true.jpg

If all lines under "Passive" must change signs, use this measure:

Importe Balance para matriz = 
VAR Pasivo = CALCULATETABLE(VALUES(BalanceResumido[Nivel I]), 
                FILTER(BalanceResumido, 
                    BalanceResumido[Nivel III] = "Pasivo"))
RETURN
           IF(COUNTROWS(Pasivo) >=1, 
                [Importe Balance Acumulado Hasta Hoy] *-1, 
                    [Importe Balance Acumulado Hasta Hoy])

final.JPG





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Fantastic! PaulDBrown.

This is just what I was looking for and I couldn't get. It was not right to incorporate the filter only for the passive and with the solution you have offered it is perfectly achieved.

Thank you very much for the help and I am at your disposal for what I can help.

Cordial greetings

Westmart.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.