Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
Sorry, it's just that seeing this image looked like the "Profit After Tax" line you wanted it positive:
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])
Proud to be a Super User!
Paul on Linkedin.
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
To learn more about Power BI, follow me on Twitter or subscribe 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
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):
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.
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.
Is this what you are looking for?
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]))
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:
2- The second picture box appears applying your proposed measure: "Matrix Balance Amount"
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.
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.
Sorry, it's just that seeing this image looked like the "Profit After Tax" line you wanted it positive:
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])
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
106 | |
104 | |
89 | |
65 |