Cross Calculation between two lines after filtering

[Anonymous]

Hi,

I need an help about the following.

I would like to perform some pricing variance comparison on my data, the query I started with is structured with the following columns:

Material	unique identifier for materials sold, may repeat several times as we sold to several customer in the different periods
Year	can be two values 2022 or 2023
Orders.Sold-To Pt	unique identifier for customer, may repeat several times as we sold several materials in the different periods
Billing quantity	whole number
ComparableLabel	defines if a sales is repeated in both years, a sales is an event where to the same customer (Orders.Sold-To Pt) has been sold the same material
Average Price	is Net Value LC BDG / Billing quantity
Concatenate	is a concatenation material+Orders.Sold-To Pt
Net Valuel LC BDG	is the revenue column

I would like to write a measure in dax or via calculated column, that will do the following operations:

step1: filters for those that in the column ComparableLabel has the value "Comparable"

step2: filters for those that has in the column "Concatenate" the same value, the visual table results should be a two liner with two different values in column Year

step3: take the average price in the column Average Price for the same row that has Year=2022

step4: take the billing quantity in the column Billing quantity for the same row that has Year=2023

step5: multiply step3 and step4 results

The idea is to evaluate the average price in the previous Year with the Billing quantity of the current one, for two values that after the initial filtering will stay always in a two liner, just not on the same line but always in the "other one".

The idea is also to have a total of this new dimension that would be comparable with the current year revenue, so also the sum need to be consistent.

I may miss some information on techniques, so that above is my interpretation - here is a data sample:

Material	Year	Orders.Sold-To Pt	Billing quantity	ComparableLabel	Average Price	Concatenate	Net Valuel LC BDG
1ABC	2023	522823	4	Not Comparable	5767.59	1ABC-522823	23070.36
2ABC	2023	102248	2	Comparable	156.22	2ABC-102248	312.44
3ABC	2022	100909	1	Comparable	140	3ABC-100909	140
3ABC	2023	100909	10	Comparable	165.8	3ABC-100909	1658

Also steps:

Step 1 filter the ComparableLabel

Material	Year	Orders.Sold-To Pt	Billing quantity	ComparableLabel	Average Price	Concatenate	Net Valuel LC BDG
2ABC	2023	102248	2	Comparable	156.22	2ABC-102248	312.44
3ABC	2022	100909	1	Comparable	8,359.12	3ABC-100909	140
3ABC	2023	100909	10	Comparable	165.8	3ABC-100909	1658

Step 2 filter the same "Concatenate" values

Material	Year	Orders.Sold-To Pt	Billing quantity	ComparableLabel	Average Price	Concatenate	Net Valuel LC BDG
3ABC	2022	100909	1	Comparable	8,359.12	3ABC-100909	140
3ABC	2023	100909	10	Comparable	165.8	3ABC-100909	1658

Step 3 identifiy 2022 Average Price in red

Material	Year	Orders.Sold-To Pt	Billing quantity	ComparableLabel	Average Price	Concatenate	Net Valuel LC BDG
3ABC	2022	100909	1	Comparable	8,359.12	3ABC-100909	140
3ABC	2023	100909	10	Comparable	165.8	3ABC-100909	1658

Step 4 identify 2023 Billing quantitiy in green

Material	Year	Orders.Sold-To Pt	Billing quantity	ComparableLabel	Average Price	Concatenate	Net Valuel LC BDG
3ABC	2022	100909	1	Comparable	8,359.12	3ABC-100909	140
3ABC	2023	100909	10	Comparable	165.8	3ABC-100909	1658

Step 4 returns 8359.12*10

Hope the sample is helping.

Thank you

Davide

[v-tangjie-msft · Accepted solution]

Hi @Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create tables.

Comparable = 
    FILTER (
        'Table',
        'Table'[ComparableLabel] = "Comparable"
    )

same Concatenate = FILTER (
    'Comparable',
    CALCULATE (
        COUNTROWS ( 'Comparable' ),
        ALLEXCEPT ( 'Comparable', 'Comparable'[Concatenate] )
    ) > 1
)

(3)We can create measures. 

Result = 
var _2022=CALCULATE(SUM('same Concatenate'[Average Price]),FILTER(ALL('same Concatenate'),'same Concatenate'[Year]=2022 && 'same Concatenate'[Concatenate]=MAX('same Concatenate'[Concatenate])))
var _2023=CALCULATE(SUM('same Concatenate'[Billing quantity]),FILTER(ALL('same Concatenate'),'same Concatenate'[Year]=2023 && 'same Concatenate'[Concatenate]=MAX('same Concatenate'[Concatenate])))
return _2022*_2023

color1 = IF(
MAX('same Concatenate'[Year])=2022,"red")

color2 = IF(
MAX('same Concatenate'[Year])=2023,"green")

(3) Setting the Conditional Format and then the result is as follows.

Best Regards,

Neeko Tang

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

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...