topic Re: calculate ratio and category percent out of total in DAX Commands and Tips

calculate ratio and category percent out of total

Premlatapandey9 — Mon, 23 Oct 2023 16:19:15 GMT

Hello Everyone I'm looking to create a DAX formula that can help me determine the distribution of different ratios between Company ID and Item ID. Specifically, I want to know how many records have a 1:1 ratio, how many have a 1:2 ratio, 1:3, and so on. Additionally, I'm interested in calculating the percentage of each category relative to the total data. For instance, I'd like to find out what percentage of the data has a 1:1 ratio, 1:5, and so forth.

Refrence Data Looks like below -

Company ID	Item ID
XYZ-23456	C1284765
WTQ-2345	C1284765
BWT-2726	C1284765
GHT-2736	C1284765
MTY-2737	C1284765
GHT-2345	C1273783
JHT-3645	C1273783
JHR-1234	C1273783
LKR-2345	C4857575
LJT-1235	C4857575
PRT-2345	C4857575

based on above data I will get the below

Item ID	Count of Company ID
C1284765	5
C1273783	3
C4857575	3

Avergae Ratio

3.66

Please help me with the Dax query to achive the above result if possible any hlep in this regard is much appreciated

Thanks and Regards

@member @help @needurgenthelp

Re: calculate ratio and category percent out of total

ThxAlot — Mon, 23 Oct 2023 17:50:27 GMT

Re: calculate ratio and category percent out of total

Jihwan_Kim — Mon, 23 Oct 2023 17:55:07 GMT

Hi, I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file if it suits your requirement.

Count company ID and ratio = IF ( HASONEVALUE ( Data[Item ID] ), FORMAT ( COUNTROWS ( DISTINCT ( Data[Company ID] ) ), "#,#0" ), FORMAT ( AVERAGEX ( DISTINCT ( Data[Item ID] ), CALCULATE ( COUNTROWS ( DISTINCT ( Data[Company ID] ) ) ) ), "#,#0.00" ) )

Re: calculate ratio and category percent out of total

Premlatapandey9 — Tue, 24 Oct 2023 06:35:11 GMT

Hello Jihwan_Kim this provides only average, I also want to know how many records of company ID to Item ID have a 1:1 ratio, how many have a 1:2 ratio, 1:3, and so on. Additionally, I'm interested in calculating the percentage of each category relative to the total data. For instance, I'd like to find out what percentage of the data has a 1:1 ratio, 1:5, and so forth, let me know if it clarify the ask or you need more details.Thank you !

Re: calculate ratio and category percent out of total

Premlatapandey9 — Wed, 25 Oct 2023 12:31:37 GMT

Greetings, everyone! I'm in search of a solution, and I would greatly appreciate any assistance. Additionally, I'm seeking guidance on how to generate a ratio column by utilizing the counts from two columns. Can anyone offer their expertise? To provide further clarification on the requirements, I'll share another data example.

I have in data	I want to get through calculation
Company ID	Item ID	Ratio coulmn	Ratio %
9987655	Abc1234	1:04	count of 1:4 devide by total	57%
8474774	Abc1234	1:04	count of 1:03 devide by total	43%
8747646	Abc1234	1:04
9009866	Abc1234	1:04
9987655	xyz1234	1:03
8474774	xyz1234	1:03
8747646	xyz1234	1:03

@member @help @needurgenthelp