help with measure

[hansaraneda]

I need to take a measure and place it in all the rows. to later perform a subtraction between two variables, I can calculate the measure but I cannot replicate it in all the rows or perform the calculation. It is not necessary to replicate the measure in all the rows, but if you do the calculation of the measure and place it in each row. please your help, thank you

12

[hansaraneda]

Hello alisson, I found the problem, what happens is that in that report I have a data segmenter of the years, but that calculation is calculating all the years for "income from ordinary activities", how can I do so that it only considers the selected year? . thanks

[hansaraneda]

reviewing more in detail I realize that it is performing a calculation, but it is not correct.

[AllisonKennedy]

@hansaraneda  You're welcome. Merry Christmas!

[AllisonKennedy · Accepted solution]

@hansaraneda You can add ALL() if you want it to be calculated on all data/rows.

previous balance amount 2 - DIVIDE(CALCULATE(SUM(Query1[saldo_anterior])),(CALCULATE(SUM(Query1[saldo_anterior]),Consultation1[Label]-"Entry of Ordinary Activities", ALL())),0)

[hansaraneda]

yeah, it was what I needed thank you very much.

[hansaraneda]

the problem is that I do that but it only calculates it on the same ignoring the rest of the rows

monto saldo anterior 2 = DIVIDE(CALCULATE(SUM(Consulta1[saldo_anterior])),(CALCULATE(SUM(Consulta1[saldo_anterior]),Consulta1[Etiqueta]= "Ingreso de actividades ordinarias")),0)

[hansaraneda]

thanks for your help but the problem has nothing to do with the dates. The issue is to calculate how much is the percentage of another line compared to the line of "income ordinary amounts". I attach an example in excel.

other income is 50.8% of other ordinary income
other expenses is 79.7 of other ordinary income
locomotion expenses is 79.7% of other ordinary income

[AllisonKennedy]

@hansaraneda 

Okay so you have: 

Revenue of Ordinary Activities = CALCULATE(SUM(Query1[saldo_anterior]),Consultation1[Label]- "Entry of Ordinary Activities")

Then you need to calculate: Other Income, Other Expenses and Locomotion Expenses. 

What column(s) in your data tell us how to find those values? For example:  

Other Income = CALCULATE(SUM(Query1[saldo_anterior]),Consultation1[Label]- "Other Income")

Then the Percent is just a DIVIDE function. 

See also if this helps, seems like you're trying to do something similar to a P&L statement? https://blog.enterprisedna.co/how-to-create-profit-and-loss-statements-in-power-bi/ 

[hansaraneda]

[AllisonKennedy]

@hansaraneda  Please can you provide more detail? Paste sample table of raw data that is in the Data Model. Also provide the current formula you're using for the measures. 

[Syndicate_Admin]

what I need is to create a measure that is the value of "revenue from ordinary activities" last year, this and then subtract it with the values ​​of last year from the rest of the rows.

I'm not sure how to share the example table with you.

attached formula

CALCULATE(SUM(Query1[saldo_anterior]),Consultation1[Label]- "Entry of Ordinary Activities")

[AllisonKennedy]

@hansaraneda  Do you have a DimDate table? 

You already have this measure:

Revenue of Ordinary Activities = CALCULATE(SUM(Query1[saldo_anterior]),Consultation1[Label]- "Entry of Ordinary Activities")

Now create another measure:

Previous Year Revenue of Ordinary Activities = CALCULATE( [Revenue of Ordinary Activities] , DATEADD(DimDate[Date], -12, Month) )

Then create the difference measure:

YoY Change Revenue Ordinary Activities = [Revenue of Ordinary Activities] - [Previous Year Revenue of Ordinary Activities]

If you don't have a date table, see these links: 

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

https://www.excel-university.com/one-click-data-model-date-table/