cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anant
Frequent Visitor

Custom operation in groupby

Hello everyone,

 

I am trying to perform a custom calculation in a groupby operation.

 

My source data looks like this:

Anant_0-1663267222173.png

I have used a couple of different tables(source data for power bi) which I have then separated into multiple fact and dimension tables using power query editor. Some of them useful for this problem statement are:

 

In the above screenshot, I have shown all the relevant columns in a single table. However they come from different tables.

 

The equipment number is part of the fact table

The ship-to customer name is part of the customer dimension table

The equipment model name is part of the equipment model dimension table

The logic_date and logic_date_year are part of the fact table

The Ageing is a number uniquely mapped to each equipment model name (meaning, each equipment model name will have a single unique Ageing number).

 

The output data format looks like this:

Anant_1-1663267475194.png

 

Now, the problem statement:

I need to find two values:

"net" and "gross" for each combination of "equipment model name" and "ship-to customer name" for the years 2021, 2022, 2023 and 2024 (i.e. previous year, current year, current year + 1 , current year + 2)

 

psuedo code: // brackets [ & ] indicate closed interval   AND   brackets ( & ) indicate open interval

for target_year in (previous year, current year, current year + 1 , current year + 2):

    for unique combination of "equipment model name" and "ship-to customer name"

       first_bucket = count of unique equipment numbers from [year = 2008(logic_date_year column) to year = (target_year- ageing) )

       second_bucket = count of unique equipment numbers from [year = (target_year - ageing) to target_year]

       net =  first_bucket - second_bucket

       gross = first_bucket

 

Example:

equipment model name = "ult freezer", ship-to customer name = "all india institute of medical", ult freezer ageing = 5

 

2021 net value = 24 indicates that:

first_bucket = total number of unique equipment numbers between years [ 2008, (2021 - 5) ) = [ 2008, 2016 ) = [ 2008, 2015 ]

second_bucket = total number of unique equipment numbers between years [ 2016, 2021 ]

2021 net value = first_bucket - second_bucket

 

2021 gross value = 62 indicates that:

2021 gross value = first_bucket

 

I have performed this activity in python and am currently facing some challenges in migrating this logic to power bi such that the values are calculated dynamically with respect to slicers and filters.

 

The second screenshot data is used as in the matrix visualization like this: (achieved from python)

Anant_0-1663268661976.png

 

 

Thank you for all the suggestions and responses in advance.

 

3 REPLIES 3
Anant
Frequent Visitor

Hi @v-yangliu-msft ,

 

Hope you are doing well. Request you to kindly help me with this.

 

Regards,

Anant

v-yangliu-msft
Community Support
Community Support

Hi  @Anant ,

I created some data:

vyangliumsft_0-1663302190141.png

Here are the steps you can follow:

1. Create calculated column.

Year =
YEAR('Table'[logic_date])

2. Create measure.

Measure_goss =
VAR _yeartoday =
    YEAR ( TODAY () )
VAR _table1 =
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Year] = _yeartoday
                    || 'Table'[Year] = _yeartoday - 1
                    || 'Table'[Year] = _yeartoday + 1
                    || 'Table'[Year] = _yeartoday + 2
            ),
            "1", [Year]
        )
    )
VAR _sum =
    CALCULATE (
        SUM ( 'Table'[gross] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[equipment model name] = MAX ( 'Table'[equipment model name] )
                && 'Table'[ship-to customer name] = MAX ( 'Table'[ship-to customer name] )
                && 'Table'[Year] = MAX ( 'Table'[Year] )
                && 'Table'[Year] IN _table1
        )
    )
RETURN
    IF (
        NOT ( ISINSCOPE ( 'Table'[ship-to customer name] ) )
            && ISINSCOPE ( 'Table'[equipment model name] ),
        CALCULATE (
            SUM ( 'Table'[gross] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[equipment model name] = MAX ( 'Table'[equipment model name] )
                    && 'Table'[Year] = MAX ( 'Table'[Year] )
                    && 'Table'[Year] IN _table1
            )
        ),
        IF (
            ISINSCOPE ( 'Table'[ship-to customer name] ),
            _sum,
            CALCULATE (
                SUM ( 'Table'[gross] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Year] = MAX ( 'Table'[Year] )
                        && 'Table'[Year] IN _table1
                )
            )
        )
    )
Measure_net =
VAR _yeartoday =
    YEAR ( TODAY () )
VAR _table1 =
    DISTINCT (
        SELECTCOLUMNS (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Year] = _yeartoday
                    || 'Table'[Year] = _yeartoday - 1
                    || 'Table'[Year] = _yeartoday + 1
                    || 'Table'[Year] = _yeartoday + 2
            ),
            "1", [Year]
        )
    )
VAR _sum =
    CALCULATE (
        SUM ( 'Table'[net] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[equipment model name] = MAX ( 'Table'[equipment model name] )
                && 'Table'[ship-to customer name] = MAX ( 'Table'[ship-to customer name] )
                && 'Table'[Year] = MAX ( 'Table'[Year] )
                && 'Table'[Year] IN _table1
        )
    )
RETURN
    IF (
        NOT ( ISINSCOPE ( 'Table'[ship-to customer name] ) )
            && ISINSCOPE ( 'Table'[equipment model name] ),
        CALCULATE (
            SUM ( 'Table'[net] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[equipment model name] = MAX ( 'Table'[equipment model name] )
                    && 'Table'[Year] = MAX ( 'Table'[Year] )
                    && 'Table'[Year] IN _table1
            )
        ),
        IF (
            ISINSCOPE ( 'Table'[ship-to customer name] ),
            _sum,
            CALCULATE (
                SUM ( 'Table'[net] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Year] = MAX ( 'Table'[Year] )
                        && 'Table'[Year] IN _table1
                )
            )
        )
    )

3. Result:

vyangliumsft_1-1663302190142.png

 

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

 

Best Regards,

Liu Yang

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

Hi @v-yangliu-msft ,

 

Thank you so much for this answer.

One thing that I would like to point out is that "net" and "gross" need to be calculated using "equipment number/sku number/catalog number/product number" column [  multiple names, same thing 🙂 ] and shouldn't change with date, only year (which you have already done).

 

Also there will be only one row for "net" and "gross" for a unique combination of ("equipment model name", "ship-to customer name", "logic_date_year") columns.

And, equipment number, ship-to customer name, equipment model name come from different tables.

 

I can provide you with the same data if required.

Was I clear above or do you still require the data

 

Regards,

Anant

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.