Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
alfertab
Helper I
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.

 

CountryDateIncomeOutcomePercentage 
England29/04/2023$8,000$6,000 
China29/04/2023$9,500$7,500 
Mexico29/04/2023$10,000$8,000 
England30/04/2023$7,500$9,000 
China30/04/2023$8,500$7,000 
Mexico30/04/2023$10,500$9,000 
England01/05/2023$11,000$8,000 
China01/05/2023$10,500$7,500 
Mexico01/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: 

alfertab_0-1708639708748.png

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

alfertab_1-1708639850208.png

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

alfertab_2-1708639995898.png

 

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
lbendlin
Super User
Super User

lbendlin_1-1708651622217.png

 

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
    )

View solution in original post

v-weiyan1-msft
Community Support
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.

vweiyan1msft_0-1708655550316.png


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.

View solution in original post

3 REPLIES 3
alfertab
Helper I
Helper I

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

v-weiyan1-msft
Community Support
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.

vweiyan1msft_0-1708655550316.png


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.

lbendlin
Super User
Super User

lbendlin_1-1708651622217.png

 

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
    )

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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