Divide a number by sum of cases

[Anonymous]

Hello guys,

I hope you can help me with this.

 Input: A table with two columns. One column shows the Product ID and the other one shows the associated total revenue.

Product ID	TOTAL REVENUE
1	100
1	100
2	300
2	300
2	300

Desired Output:  The TOTAL REVENUE should be devided the the frequency the assocated product ID appears in Column 1.

E.g. ID 1 appears two times in column 1. So the total revenue (100) should be divided by 2 and added to the third column (Split Revenue)

ID 2 appears three times in column 1. So the total revenue  (300) should be devided by 3 and added to the third column (Split Revenue)

PRODUCT ID	TOTAL REVENUE	SPLIT REVENUE
1	100	50
1	100	50
2	300	100
2	300	100
2	300	100

Thanks so much!

[Anonymous · Accepted solution]

Go to Add Column in the data tab and paste in the following DAX code. This will create a new column straight into your table. 

Column =
VAR __Occurence =
    COUNTX (
        FILTER (
            'associated total revenue',
            EARLIER ( 'associated total revenue'[Product ID] ) = 'associated total revenue'[Product ID]
        ),
        'associated total revenue'[Product ID]
    )
RETURN
    'associated total revenue'[TOTAL REVENUE] / __Occurence

[ilton]

Hi! @Anonymous 

Try

let
    Source = Table.FromRecords({
        [PRODUCT ID=1,TOTAL REVENUE=100],
        [PRODUCT ID=1,TOTAL REVENUE=100],
        [PRODUCT ID=2,TOTAL REVENUE=300],
        [PRODUCT ID=2,TOTAL REVENUE=300],
        [PRODUCT ID=2,TOTAL REVENUE=300]
    }),
    #"Grouped Rows" = Table.Group(Source, {"PRODUCT ID"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Join Tables" = Table.Join(Source, "PRODUCT ID", #"Grouped Rows", "PRODUCT ID", JoinKind.Inner),
    #"Added Custom" = Table.AddColumn(#"Join Tables", "SPLIT REVENUE", each Value.Divide([TOTAL REVENUE], [Count])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"})
in
    #"Removed Columns"

[Anonymous]

Hey @ilton , 

How did you do this step in Power Q. It looks like you've merged the dataset with itself however minus a step or 2. 

Is there a pre-built function for this in Power Q?

= Table.Join(Source, "PRODUCT ID", #"Grouped Rows", "PRODUCT ID", JoinKind.Inner)

[ilton · Accepted solution]

Hi! I Grouped the tabel by "PRODUCT ID" and counted the rows.

#"Grouped Rows" = Table.Group(Source, {"PRODUCT ID"}, {{"Count", each Table.RowCount(_), Int64.Type}})

then joined the original source with the grouped source.

Please consider marking as answer if this resolved your problem.

[Anonymous · Accepted solution]

Go to Add Column in the data tab and paste in the following DAX code. This will create a new column straight into your table. 

Column =
VAR __Occurence =
    COUNTX (
        FILTER (
            'associated total revenue',
            EARLIER ( 'associated total revenue'[Product ID] ) = 'associated total revenue'[Product ID]
        ),
        'associated total revenue'[Product ID]
    )
RETURN
    'associated total revenue'[TOTAL REVENUE] / __Occurence