calculate % Product codes not sold in all territories?

[Maha_0221]

Expected output

Sample data:

QUANTITYORDERED 30 34 41 45 49 36 29 48 22

PRICEPERUNIT 95.7 81.35 94.74 83.26 100 96.66 86.13 100 98.57

ORDERLINENUMBER 2 5 2 6 14 1 9 1 2

SALES 2871 2765.9 3884.34 3746.7 5205.27 3479.76 2497.77 5512.32 2168.54

ORDERDATE 12/31/2019 0:00 3/12/2020 0:00 5/6/2020 0:00 6/30/2020 0:00 8/15/2020 0:00 9/2/2020 0:00 9/16/2020 0:00 9/23/2020 0:00 10/6/2020 0:00

PRODUCTLINE Motorcycles Motorcycles Motorcycles Motorcycles Motorcycles Motorcycles Motorcycles Motorcycles Motorcycles

MSRP 95 95 95 95 95 95 95 95 95

PRODUCTCODE S10_1678 S10_1678 S10_1678 S10_1678 S10_1678 S10_1678 S10_1678 S10_1678 S10_1678

COUNTRY USA France France USA USA USA France Norway USA

TERRITORY NA EMEA EMEA NA NA NA EMEA EMEA NA

[v-nuoc-msft · Accepted solution]

Hi @Maha_0221 

@lbendlin @xifeng_L Thank you very much for your prompt reply, and here allow me to share some of the content.

Since you didn't explain the calculation logic, here I assume the following:

RESULT = 
var TotalProduct = COUNTROWS('Table')
var selectProductCode = 
    CALCULATE(
        COUNTROWS('Table'), 
        FILTER(
            ALL('Table'), 
            'Table'[TERRITORY] <> "NA" //You can change the filter, for example: 'Table'[TERRITORY] <> "APAC"
        )
    )
RETURN selectProductCode / TotalProduct

Here is the result.

Regards,

Nono Chen

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

[xifeng_L]

The example data has only one product code, can you provide more example data and pbix files?

And it would be nice to have a demonstration of the calculation logic.

[Maha_0221]

Sample data:

ORDERNUMBER	QUANTITYORDERED	PRICEPERUNIT	ORDERLINENUMBER	SALES	ORDERDATE	STATUS	PRODUCTLINE	MSRP	PRODUCTCODE	COUNTRY	TERRITORY
10424	50	100	6	12001	4/6/2022 0:00	In Process	Classic Cars	214	S10_1949	Spain	EMEA
10425	38	100	12	5894.94	4/6/2022 0:00	In Process	Classic Cars	147	S10_4962	France	EMEA
10424	49	100	3	7969.36	4/6/2022 0:00	In Process	Trucks and Buses	136	S12_1666	Spain	EMEA
10425	33	100	4	4692.6	4/6/2022 0:00	In Process	Trucks and Buses	118	S12_4473	France	EMEA
10424	54	100	5	7182	4/6/2022 0:00	In Process	Trucks and Buses	116	S18_1097	Spain	EMEA
10422	51	95.55	2	4873.05	4/5/2022 0:00	In Process	Vintage Cars	102	S18_1342	USA	NA
10422	25	51.75	1	1293.75	4/5/2022 0:00	In Process	Vintage Cars	53	S18_1367	USA	NA
10420	37	100	5	5283.6	4/4/2022 0:00	In Process	Vintage Cars	170	S18_1749	Australia	APAC
10425	28	100	3	5318.04	4/6/2022 0:00	In Process	Classic Cars	163	S18_2238	France	EMEA
10420	36	63.57	4	2288.52	4/4/2022 0:00	In Process	Vintage Cars	60	S18_2248	Australia	APAC
10425	38	99.41	7	3777.58	4/6/2022 0:00	In Process	Trucks and Buses	122	S18_2319	France	EMEA

Sample

[lbendlin]

please do not post links to your reports. 

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[Maha_0221]

@lbendlin, @Greg_Deckler , @xifeng_L @Hima_Bindu @EverydayBI , @MSmith_iTalent