Create new column from values within table

[Anonymous]

Hi All,

I would like to create another column that takes the Good Parts value from theLine D10Cell4 and divides it with the quantitiy value from theLine D10Cell1 of that date. That's the method I have been trying and have labeled it 1st in the picture below.

I believe algebraically you can get the same value by adding the Bad % for that day. I have labeled that 2nd in the picture below.

Let me know if I can be more clear.

I will attach csv file.

Thanks!

Month	Day	theLine	quantity	Good Parts	FPY %	Bad Parts	Bad %
February	2	D10Cell1	15798	15775	99.85%	23	0.15%
February	2	D10Cell2	15737	15286	97.13%	451	2.87%
February	2	D10Cell3	15288	15083	98.66%	205	1.34%
February	2	D10Cell4	15059	14469	96.08%	590	3.92%
February	3	D10Cell1	18262	18149	99.38%	113	0.62%
February	3	D10Cell2	18081	17637	97.54%	444	2.46%
February	3	D10Cell3	17514	17293	98.74%	221	1.26%
February	3	D10Cell4	17203	16492	95.87%	711	4.13%
February	4	D10Cell1	27201	27052	99.45%	149	0.55%
February	4	D10Cell2	26996	26090	96.64%	906	3.36%
February	4	D10Cell3	26015	25654	98.61%	361	1.39%
February	4	D10Cell4	25547	24891	97.43%	656	2.57%

[v-easonf-msft · Accepted solution]

Hi , @Anonymous 

Create  a calculated column as below:

1st Column =
VAR Quantity1 =
    CALCULATE (
        SUM ( 'Table'[quantity] ),
        FILTER (
            'Table',
            'Table'[Day] = EARLIER ( 'Table'[Day] )
                && 'Table'[theLine] = "D10Cell1"
        )
    )
VAR GoodParts =
    CALCULATE (
        SUM ( 'Table'[Good Parts] ),
        FILTER (
            'Table',
            'Table'[Day] = EARLIER ( 'Table'[Day] )
                && 'Table'[theLine] = "D10Cell4"
        )
    )
RETURN
    DIVIDE ( GoodParts, Quantity1 )

Here is  sample ：

URL:

https://wicren-my.sharepoint.com/:u:/g/personal/michael_wicren_onmicrosoft_com/EY_oFBGTlqZBocDvCYn3eycB4zG_9paEjXWU4ARWcNjGsw?e=xMxkUK 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

I think it is a case of category min-max, where category is day,

Refer :https://community.powerbi.com/t5/Desktop/highest-value-by-category/td-p/428758

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin