cancel
Showing results for
Did you mean:
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:

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:

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)

Thank you for all the suggestions and responses in advance.

3 REPLIES 3
Frequent Visitor

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

Regards,

Anant

Community Support

Hi  @Anant ,

I created some data:

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:

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

Frequent Visitor

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

Announcements

#### 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.

#### 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!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors