How can I calculate total by categories that a supplier works with?

Question

I have a slicer that will filter the whole page by Supplier, in the first visual (multi-row card), I have to calculate the participation (percent) of the supplier in the whole company and also the participation of this supplier just in the categories he works with.

My table is similar to this:

Supplier	Category	Revenue
Supplier1	Hair	10000
Supplier2	Intimate Hygiene	10000
Supplier2	Dermocosmetics	15000
Supplier1	Intimate Hygiene	20000
Supplier3	Hair	15000
Supplier3	Intimate Hygiene	20000

So, suppose I'm filtering Supplier1, so I want to get the sum of the revenue of Supplier1 divided by the sum of revenue of all suppliers just in the categories Supplier1 participates. As Supplier 1 just participates in Hair and Intimate Hygiene, the number should be:

(10000+20000)/(10000+20000+10000+20000+15000+20000)=31.57%

Notice that Supplier1 does not have revenue values in Dermocosmetics, so I discard the revenue of this category in the calculation I'm doing.

Answer 1

Assuming your data is the following

Table

Supplier	Category	Revenue
Supplier1	Hair	10000
Supplier2	Intimate Hygiene	10000
Supplier2	Dermocosmetics	15000
Supplier1	Intimate Hygiene	20000
Supplier3	Hair	15000
Supplier3	Intimate Hygiene	20000

The share for each supplier should be

• Supplier 1: 40% (30,000/75,000)

• Supplier 2: 38.46% (25,000/65,000)

• Supplier 3: 46.67% (35,000/75,000)

DAX Calculation

The first step is to look for the categories of each selected supplier. Later on apply the categories found into a CALCULATE with SUM.

Supplier Share = 
VAR _SelectedSupplier =
    SELECTEDVALUE ( 'Table'[Supplier] )
VAR _SupplierCategories =
    SELECTCOLUMNS (
        FILTER ( 'Table', [Supplier] = _SelectedSupplier ),
        "@Categories", [Category]
    )
VAR _TotalRevenue =
    CALCULATE (
        SUM ( 'Table'[Revenue] ),
        'Table'[Category] IN _SupplierCategories,
        REMOVEFILTERS ( 'Table'[Supplier] )
    )
VAR _SelectedSupplierRevenue =
    SUM ( 'Table'[Revenue] )
VAR _Result =
    DIVIDE ( _SelectedSupplierRevenue, _TotalRevenue )
RETURN
    _Result

Output