Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello
i have sample data and i want to find our connt of invoice_number which are Net_amt >= 4000 store_code wise and also below net_amt<4000 store wise. as you see same invoice number but from different store and has different Net_amt
INVOICE_Number INVOICE_DT NET_AMT STORE_CODE
0324041700032 2024-04-17 1169 100010008
0324041700032 2024-04-17 1505 100010011
0324041700032 2024-04-17 4830 100010015
0324041700032 2024-04-17 801 100010016
0324041700032 2024-04-17 481 100010017
0324041700032 2024-04-17 13340 100010021
0324041700032 2024-04-17 1760 100010025
0324041700032 2024-04-17 4000 100010027
so expected resule
for >=4000 count of invoice number would be 3 which are
INVOICE_Number INVOICE_DT NET_AMT STORE_CODE
0324041700032 2024-04-17 4830 100010015
0324041700032 2024-04-17 13340 100010021
0324041700032 2024-04-17 4000 100010027
for <4000 count of invoice number would be 5 which are
INVOICE_Number INVOICE_DT NET_AMT STORE_CODE
0324041700032 2024-04-17 1169 100010008
0324041700032 2024-04-17 1505 100010011
0324041700032 2024-04-17 801 100010016
0324041700032 2024-04-17 481 100010017
0324041700032 2024-04-17 1760 100010025
thanks
Solved! Go to Solution.
ops
i want count of invoice Number as same invoice is created in different store and with different number. so i want filter them by store and >= 4000 to find out count of invoice number how many invoices are there over or equal to 4000
thanks
thnaks
Hi,
Write these measures
Net amount = sum(Data[Net_amt])
Count = countrows(filter(values(Data[Store_code]),[Net amount]>=4000)
Count1 = countrows(filter(values(Data[Store_code]),[Net amount]<4000)
Drag both these measures to a card visual.
thanks for your reply @Ashish_Mathur
this return me only 76 but as per MSSQL query i should have 5932 for month of january 2024
thanks
My formula will give you the count of unique Stores.
ops
i want count of invoice Number as same invoice is created in different store and with different number. so i want filter them by store and >= 4000 to find out count of invoice number how many invoices are there over or equal to 4000
thanks
thnaks
i got the solution,