Share and contribution calculations

[Aditya_Nayak]

Hi Power BI Community, please help me with the calculation logic in Power BI for columns "Share" and "Contribution" in the attached screenshot. I have mentioned the formula in the relevant cells of the table to make it easier to understand the ask.

Thanks in advance! Happy New Year!

- Aditya Nayak

[v-yanjiang-msft · Accepted solution]

Hi @Aditya_Nayak ,

As all the products are in the same column, they are in the same level in your sample, you cann't directly divide them, here's my solution.

1.Create another table indicating the relationship between the product and its superior product, and make relationship between the two tables.

2.Create two measures.

Contribution = 
DIVIDE (
    MAX ( 'Table'[Sales] ),
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Geography] = MAX ( 'Table'[Geography] )
                && 'Table'[Product] = MAX ( 'Table (2)'[Upper level product] )
        ),
        'Table'[Sales]
    )
)

Share = 
IF (
    MAX ( 'Table'[Product] )
        IN { "Manufacture Furniture", "Manufacture Tables", "Manufacture Chairs" },
    [Contribution],
    BLANK ()
)

3.Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Aditya_Nayak]

Hi @v-yanjiang-msft ,

Yes - this worked. Thanks for this solution! 🙂

Regards,

Aditya Nayak

[Aditya_Nayak]

Hi @amitchandak ,

I've added a few details to clarify the ask. Please refer the attached image. Looking forward to a quick solution.

Thanks!

[Aditya_Nayak]

Geography	Product	Sales	Share	Contribution
All Locations	Total Furniture	100	-	100/100
All Locations	Manufacturer Furniture	60	60/100	60/100
All Locations	Sub-Category Chairs	90	-	90/100
All Locations	Manufacturer Chairs	40	40/90	40/90
All Locations	Sub-Category Tables	70	-	70/100
All Locations	Manufacturer Tables	50	50/70	50/70

---

@Aditya_Nayak wrote:

Hi Power BI Community, please help me with the calculation logic in Power BI for columns "Share" and "Contribution" in the attached

---

@Aditya_Nayak wrote:

Hi Power BI Community, please help me with the calculation logic in Power BI for columns "Share" and "Contribution" in the attached screenshot. I have mentioned the formula in the relevant cells of the table to make it easier to understand the ask.

Thanks in advance! Happy New Year!

- Aditya Nayak

---

screenshot. I have mentioned the formula in the relevant cells of the table to make it easier to understand the ask.

Thanks in advance! Happy New Year!

- Aditya Nayak

---

Thanks @amitchandak ! I tried both the statements that you mentioned, but didn't get the desired results. Please refer the attached table for sample data and sample output. Thanks!

[amitchandak]

@Aditya_Nayak , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Seem like

divide(sum(Table[Sale]), calculate(sum(Table[Sale]),allexcept(Table, Table[geography], Table[Product]))

or

divide(sum(Table[Sale]), calculate(sum(Table[Sale]),filter(allselected(Table), Table[geography] = max(Table[geography]) && Table[Product] = max(Table[Product]) ))