Filter and substracting in rows with dax

[Simple_tuition1]

Hi,

I need your help. I hope somebody can help.. This is the following dummy data. Each sales Person is in multiple countries active and has an overall inventory of products and they are selling extension passes like subscriptions for these products. There are Countries where some sales person is not active or just not active for a certain product type. Therefore they don't posses any values regarding sales and forcast. For example Mary is not active in Canada for City Bike and John ist not active in Italy overall. Therefore there are also no rows of data for these kind of scenarios. 

Data:

Product	Salesperson	Country	Overall Inventory	Sold Extension Pass	Forcast Sales Extension Pass
City Bike	Gina	Canada	5400	232	400
Mountain Bike	Gina	Canada	2323	2800	3210
City Bike	Gina	Italy	5400	213	429
Mountain Bike	Gina	Italy	2323	1050	1100
City Bike	Gina	USA	5400	122	200
Mountain Bike	Gina	USA	2323	2150	2699
City Bike	John	Canada	4323	333	344
Mountain Bike	John	Canada	2133	442	688
City Bike	John	USA	4323	3300	4000
Mountain Bike	John	USA	2133	7300	8000
City Bike	Mary	Italy	2321	213	322
Mountain Bike	Mary	Italy	2312	600	800
City Bike	Mary	USA	2321	123	400
Mountain Bike	Mary	Canada	2312	442	600
Mountain Bike	Mary	USA	2312	600	760
City Bike	Overall	Canada	12044	565	744
Mountain Bike	Overall	Canada	6768	3684	4498
City Bike	Overall	Italy	12044	426	751
Mountain Bike	Overall	Italy	6768	1650	1900
City Bike	Overall	USA	12044	3545	4600
Mountain Bike	Overall	USA	6768	10050	11459

The visualization in a matrix looks like this:

Now the challenge is that I want to calculate the percentage of the overall amount, which is through Sold / overall inventory. But since Mary is not active in Canada for example, I need to filter those Salesperson, which are not active in each country and then substract for example the overall inventory for City Bike in Canada for Mary (12044-2321). The overall inventory should only consist of active Salespersons in the country. How can I do this with a measure or calculated column in Power BI? If possible I would like to avoid power query since it's already complicated enough regarding transformation in my model.  

[v-rzhou-msft · Accepted solution]

Hi @Simple_tuition1 ,

I suggest you to try this code to create a measure.

Percentage =
VAR _Namelist =
    CALCULATETABLE (
        VALUES ( 'Table'[Salesperson] ),
        ALLEXCEPT ( 'Table', 'Table'[Product], 'Table'[Country] ),
        'Table'[Salesperson] <> "Overall"
    )
VAR _NewOverall =
    CALCULATE (
        SUM ( 'Table'[Overall Inventory] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Product], 'Table'[Country] ),
            'Table'[Salesperson] IN _NAMELIST
        )
    )
VAR _Sold =
    CALCULATE ( SUM ( 'Table'[Sold Extension Pass] ) )
RETURN
    DIVIDE ( _Sold, _NewOverall )

Result is as below.

Best Regards,
Rico Zhou

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