Compare values from two tables and get count...

Question

Hello,&nbsp;I have two tables,First one is... Sales,SaleID_Product Type_Customer_Discount1ARam152BRam163BKrishna174CKrishna185DKrishna196ARamesh207BRamesh218BRamesh229BRamesh2310CKaplesh2411CKaplesh2512DKaplesh26&nbsp;and the second one is... Stock,StockID_Product Type_Discount1A102A113A124A135A146B157B168B179B1810B1911C2012C2113C2214C2315C2416D2517D2618D2719D2820D29&nbsp;Both tables are connected to [Product Type] column.&nbsp;I want last column in below table,&nbsp;Count of Unique [StockID] with one condition that Stock [Discount] must be greater than Sold [Discount]&nbsp;&nbsp;Customer_Total Sold Quantity_Available Stock_Actual Stock QuantityRam2100(A)+3(B) = 3Krishna3152(B)+5(C)+5(D) = 12Ramesh4150(A)+ 0(all 3 B types) = 0Kalpesh3100(all 2 C type) + 3(D) = 3&nbsp;I want the highlighted in Red colour values, extra details are just explaination.&nbsp;

anonymous · Accepted Answer

Hi&nbsp;ddpl&nbsp;,
Please try below steps:
1. create a measure with below dax formula
Measure =
VAR cur_customer =
    SELECTEDVALUE ( Sales[Customer_] )
VAR tmp1 =
    SELECTCOLUMNS (
        Stock,
        "Stock ID", [StockID_],
        "Product Type", [Product Type_],
        "Count", [Discount]
    )
VAR tmp2 =
    CROSSJOIN ( Sales, tmp1 )
VAR tmp3 =
    CALCULATETABLE (
        VALUES ( Stock[StockID_] ),
        FILTER (
            tmp2,
            [Customer_] = cur_customer
                &amp;&amp; [Product Type] = [Product Type_]
                &amp;&amp; [Count] &gt; [Discount]
        )
    )
VAR _val =
    COUNTROWS ( tmp3 )
RETURN
    IF ( ISBLANK ( _val ), 0, _val )

2. add a table visual&nbsp; with field and measure&nbsp;

Please refer the attached .pbix file.
&nbsp;
Best regards,Community Support Team_Binbin YuIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Compare values from two tables and get count...

2 Replies

Related content

Count showing wrong compare to bar chart

Count of values not accurate

Counting nulls

Count duplicate values

Comparing Date Counts

SaleID_	Product Type_	Customer_	Discount
1	A	Ram	15
2	B	Ram	16
3	B	Krishna	17
4	C	Krishna	18
5	D	Krishna	19
6	A	Ramesh	20
7	B	Ramesh	21
8	B	Ramesh	22
9	B	Ramesh	23
10	C	Kaplesh	24
11	C	Kaplesh	25
12	D	Kaplesh	26

StockID_	Product Type_	Discount
1	A	10
2	A	11
3	A	12
4	A	13
5	A	14
6	B	15
7	B	16
8	B	17
9	B	18
10	B	19
11	C	20
12	C	21
13	C	22
14	C	23
15	C	24
16	D	25
17	D	26
18	D	27
19	D	28
20	D	29

Customer_	Total Sold Quantity_	Available Stock_	Actual Stock Quantity
Ram	2	10	0(A)+3(B) = 3
Krishna	3	15	2(B)+5(C)+5(D) = 12
Ramesh	4	15	0(A)+ 0(all 3 B types) = 0
Kalpesh	3	10	0(all 2 C type) + 3(D) = 3