Division result categorization with data from 2 different tables.

Question

Hello,I have the following tables:F_NEWBUSINESS&nbsp;DELIVERY_DATEACCT_IDMAIN_DEALER_CODE31/01/20231A31/01/20232A31/01/20233B28/02/20234C28/02/20235A28/02/20236BF_DEALERSALES&nbsp;DELIVERY_DATEIDMAIN_DEALER_CODERETAIL_SOLD31/01/20231A331/01/20232A531/01/20233B228/02/20234C128/02/20235A028/02/20236C4&nbsp;I am trying to categorize the result of the division:count of newbusiness/sum of retail sold, by main_dealer_code&nbsp;To calculate the division I have this metric:&nbsp;Dealer Fin Pen =&nbsp;DIVIDE(&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; COUNT(F_newbusiness[acct_id]),&nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; F_NEWBUSINESS,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; F_NEWBUSINESS[MAIN_DEALER_CODE]&nbsp; &nbsp; &nbsp; &nbsp; ) &nbsp; &nbsp;&nbsp; &nbsp; ),&nbsp; &nbsp; CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; SUM(F_dealersales[retail_sold]),&nbsp; &nbsp; &nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; F_DEALERSALES,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; F_DEALERSALES[MAIN_DEALER_CODE]&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; ))I need to show in a Line Chart visual the amount of main dealers that fall under each category with each category being one line in the chart and with the categories being the following:- dealers FP &lt;20%&nbsp; when&nbsp;Dealer Fin Pen &lt; 0.2- dealers FP 20-40% when&nbsp;Dealer Fin Pen &gt; 0.2 &amp;&amp;&nbsp;Dealer Fin Pen &lt;= 0.4- dealers FP &gt;40%&nbsp;when&nbsp;Dealer Fin Pen &gt; 0.4This last part is where I am having troubles, I am not able to count the amount of main dealers that falls under each category.Any tips or ideas on how can i do this?&nbsp;Thanks in advance!

rubayatyasmin · Accepted Answer

Hi,&nbsp;Anonymous&nbsp;
&nbsp;
try this one.&nbsp;
Count Dealers FP &lt;20% = CALCULATE(COUNTROWS(F_NEWBUSINESS), FILTER(ALL(F_NEWBUSINESS),[Dealer Fin Pen] &lt; 0.2))
Count Dealers FP 20-40% = CALCULATE(COUNTROWS(F_NEWBUSINESS), FILTER(ALL(F_NEWBUSINESS),[Dealer Fin Pen] &gt;= 0.2 &amp;&amp; [Dealer Fin Pen] &lt;= 0.4))
Count Dealers FP &gt;40% = CALCULATE(COUNTROWS(F_NEWBUSINESS), FILTER(ALL(F_NEWBUSINESS),[Dealer Fin Pen] &gt; 0.4))

Forum Discussion

Division result categorization with data from 2 different tables.

2 Replies

Related content

Dynamic RLS for Division-Based Access Using USERPRINCIPALNAME() and Multiple Division Memberships

Dax - division of different but related items

division by zero

Different schema into different destination

Division

DELIVERY_DATE	ACCT_ID	MAIN_DEALER_CODE
31/01/2023	1	A
31/01/2023	2	A
31/01/2023	3	B
28/02/2023	4	C
28/02/2023	5	A
28/02/2023	6	B

DELIVERY_DATE	ID	MAIN_DEALER_CODE	RETAIL_SOLD
31/01/2023	1	A	3
31/01/2023	2	A	5
31/01/2023	3	B	2
28/02/2023	4	C	1
28/02/2023	5	A	0
28/02/2023	6	C	4