Forum Discussion

srkase's avatar
srkase
Helper IV
6 years ago
Solved

MULTIPLE FILTRATION

I have the data like this...   YEAR SHORTNAME DEALER TYPE PRODUCT APR MAY JUN JUL AUG SEP OCT NOV DEC JAN FEB MAR TOT CATEGORY 20182019 AB A STD PROD 10 0 0 0 0 0 ...
Need Help
  • srkase's avatar
    srkase
    6 years ago
    v-chuncz-msft wrote:

    srkase 

     

    You may use the following measure.

    Measure =
COUNTROWS (
    FILTER ( VALUES ( cab[DEALER] ), CALCULATE ( SUM ( cab[QTY] ) <= 1000 ) )
)

    it gives only true or false...

     

    I applied this measure ... and works fine..

     

    qty500 = CALCULATE (
    DISTINCTCOUNT('BREAKUP CABLES'[DEALER] ),
    FILTER ( ALL ( 'BREAKUP CABLES'[DEALER] ), 'BREAKUP CABLES'[sumqty]<=499))

     

mussaenda's avatar
mussaenda
Community Champion
6 years ago

If you will try it on the data you gave, it will work.

 

if it didn't work, you should at least explain why or what problem you have encountered so it can be resolved.

 

Thank you.

srkase's avatar
srkase
Helper IV
6 years ago

THIS WAS THE SAMPLE DATA

 

YEARSHORTNAMEDEALERTYPEPRODUCTCATEGORYMONTHQTY
20192020CBEASTDACABLEAPR1000
20192020CBEASTDBCABLEAPR3000
20192020CBEASTDACABLEAPR1738
20192020CBEASTDBCABLEAPR3000
20192020CBEASTDACABLEAPR3940
20192020CBEBSTDBCABLEAPR1000
20192020CBECSTDACABLEAPR1000
20192020CBECSTDBCABLEAPR1800
20192020CBECSTDACABLEAPR3000
20192020CBEDSTDBCABLEAPR1000
20192020CBEDSTDACABLEAPR1000
20192020CBEDSTDBCABLEAPR2869
20192020CBEDSTDACABLEAPR3000
20192020CBEESTDBCABLEAPR1000
20192020CBEESTDACABLEAPR872
20192020CBEESTDBCABLEAPR4000
20192020CBEFSTDACABLEAPR1000
20192020CBEFSTDBCABLEAPR2000
20192020CBEGSTDACABLEAPR1000
20192020CBEGSTDBCABLEAPR1000
20192020CBEGSTDACABLEAPR2000
20192020CBEGSTDBCABLEAPR6000
20192020CBEHSTDACABLEAPR2000
20192020CBEISTDBCABLEAPR2000
20192020CBEISTDACABLEAPR3877
20192020CBEISTDBCABLEAPR4000
20192020CBEJSTDACABLEAPR1000
20192020CBEJSTDBCABLEAPR2000
20192020CBEJSTDACABLEAPR6000
20192020CBEKSTDBCABLEAPR1000
20192020CBEKSTDACABLEAPR4000
20192020CBELSTDBCABLEAPR1000
20192020CBELSTDACABLEAPR890
20192020CBEMSTDBCABLEAPR2000
20192020CBEMSTDACABLEAPR6000
20192020CBEMSTDBCABLEAPR12000
20182019CBEMSTDACABLEAPR6000
20182019CBEMSTDBCABLEAPR12000
20182019CBEMSTDACABLEAPR1000
20182019CBEMSTDBCABLEAPR3000
20182019CBEMSTDACABLEAPR1738
20182019CBEMSTDBCABLEAPR3000
20182019CBEMSTDACABLEAPR3940
20182019CBELSTDBCABLEAPR1000
20182019CBEKSTDACABLEAPR1000
20182019CBEKSTDBCABLEAPR1800
20182019CBEKSTDACABLEAPR3000
20182019CBEISTDBCABLEAPR1000
20182019CBEISTDACABLEAPR1000
20182019CBEISTDBCABLEAPR2869
20182019CBEISTDACABLEAPR3000
20182019CBEJSTDBCABLEAPR1000
20182019CBEJSTDACABLEAPR872
20182019CBEJSTDBCABLEAPR4000
20182019CBEASTDACABLEAPR2000
20182019CBEASTDBCABLEAPR6000
20182019CBEASTDACABLEAPR12000
20182019CBEASTDBCABLEAPR6000
20182019CBEASTDACABLEAPR12000

 

The measure i have used to filter <1000,

Qty<1000 = CALCULATE(COUNT('cab'[DEALER]),cab[QTY] <=1000)
 
the result i get is
 
Month  Count of Dealer   Qty<1000
April              13                       21
 
I have kept distinct count of dealer
 
Results are wrong
 
 

 

  • srkase's avatar
    srkase
    Helper IV
    6 years ago

    In a month a dealer might have taken items in many invoices... The sum we should check and get the total and then the measures should be applied..

     

    • v-chuncz-msft's avatar
      v-chuncz-msft
      Community Support
      6 years ago

      srkase 

       

      You may use the following measure.

      Measure =
COUNTROWS (
    FILTER ( VALUES ( cab[DEALER] ), CALCULATE ( SUM ( cab[QTY] ) <= 1000 ) )
)
      • srkase's avatar
        srkase
        Helper IV
        6 years ago
        v-chuncz-msft wrote:

        srkase 

         

        You may use the following measure.

        Measure =
COUNTROWS (
    FILTER ( VALUES ( cab[DEALER] ), CALCULATE ( SUM ( cab[QTY] ) <= 1000 ) )
)

        it gives only true or false...

         

        I applied this measure ... and works fine..

         

        qty500 = CALCULATE (
        DISTINCTCOUNT('BREAKUP CABLES'[DEALER] ),
        FILTER ( ALL ( 'BREAKUP CABLES'[DEALER] ), 'BREAKUP CABLES'[sumqty]<=499))