cancel
Showing results for
Did you mean:

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.

Helper I

## Calculate de division of one row vs other row with conditionals

Hi everybody, I'm having some troubles with a calculation I need to do and I would like to know if someone knows what to do.

Basically I have the following table with daily data of different countries.

 Country Date Income Outcome Percentage England 29/04/2023 \$8,000 \$6,000 China 29/04/2023 \$9,500 \$7,500 Mexico 29/04/2023 \$10,000 \$8,000 England 30/04/2023 \$7,500 \$9,000 China 30/04/2023 \$8,500 \$7,000 Mexico 30/04/2023 \$10,500 \$9,000 England 01/05/2023 \$11,000 \$8,000 China 01/05/2023 \$10,500 \$7,500 Mexico 01/05/2023 \$14,500 \$11,000

What I need to calculate is the Percentage of the Outcome compared to the next day's income; in case there is no data for the next day, the result of the outcome/income for that same day must be calculated. For example, the value for England(29/04/2023) must be calculated as following:

Another example could be the value for China (30/04/2023):

The last example could be the value for Mexico (01/05/2023), where we don't have data for the next day:

The calculation needs to be an extra column on the table. At first I was using Index in Power Query, but the problem is that in some future there may be data from more countries and I think using index there would be errors. Does anyone know how to do it? It could be as a column calculated in Power Query or in Power BI. Thanks.

2 ACCEPTED SOLUTIONS
Super User

``````Percentage =
VAR d = [Date]
VAR c = [Country]
RETURN
DIVIDE (
[Outcome],
COALESCE (
CALCULATE (
SUM ( 'Table'[Income] ),
ALL ( 'Table' ),
'Table'[Country] = c,
'Table'[Date] = d + 1
),
[Income]
),
0
)``````
Community Support

Hi @alfertab ,

@lbendlin nice method! Thank you, for your quick response and the solution provided.
And based on the sample and description you provided, you can also try code as below to create a Calculated column.

``````Percentage =
VAR CurrentCountry = 'Table'[Country]
VAR CurrentOutCome = 'Table'[Outcome]
VAR NextDate = NEXTDAY('Table'[Date])
VAR NextIncome =
CALCULATE (
MAX ( 'Table'[Income] ),
FILTER (
ALL ( 'Table' ),
'Table'[Country] = CurrentCountry
&& 'Table'[Date] = NextDate
)
)
RETURN
IF (
ISBLANK ( NextIncome ),
DIVIDE ( CurrentOutCome, 'Table'[Income] ),
DIVIDE ( CurrentOutCome, NextIncome )
)
``````

Result is as below.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Helper I

@lbendlin @v-weiyan1-msft Thanks both of you for the answers :D. Both methods were usefull and worked. Thanks a lot.

Community Support

Hi @alfertab ,

@lbendlin nice method! Thank you, for your quick response and the solution provided.
And based on the sample and description you provided, you can also try code as below to create a Calculated column.

``````Percentage =
VAR CurrentCountry = 'Table'[Country]
VAR CurrentOutCome = 'Table'[Outcome]
VAR NextDate = NEXTDAY('Table'[Date])
VAR NextIncome =
CALCULATE (
MAX ( 'Table'[Income] ),
FILTER (
ALL ( 'Table' ),
'Table'[Country] = CurrentCountry
&& 'Table'[Date] = NextDate
)
)
RETURN
IF (
ISBLANK ( NextIncome ),
DIVIDE ( CurrentOutCome, 'Table'[Income] ),
DIVIDE ( CurrentOutCome, NextIncome )
)
``````

Result is as below.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

``````Percentage =
VAR d = [Date]
VAR c = [Country]
RETURN
DIVIDE (
[Outcome],
COALESCE (
CALCULATE (
SUM ( 'Table'[Income] ),
ALL ( 'Table' ),
'Table'[Country] = c,
'Table'[Date] = d + 1
),
[Income]
),
0
)``````

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors