Range Creation with filter and all except

1. We need t create a bar chart with range in the Axis and the distinct count of the company_name on the value field.
2. The range should have three buckets
   1. 0-50k
   2. 50001k-100k
   3. Above 100k
3. So, we need to count the companies that have made transactions in these range buckets.
4. The SQL equivalent for this result required is as below:

select count( distinct [COMPANY_NAME]) total,'Range:0-50K' as range from (

select sum(amount) sums,COMPANY_NAME

from Xchange

where TNX_STAT_CODE=4  and [INP_USER_ID]>0

AND  appl_date >=(select dateadd(month,datediff(month,0,getdate())-12,0)) AND appl_date <=(select getdate())

and To_CCY='USD'

group by COMPANY_NAME

having sum(amount)<=50000)l

union all

select count( distinct [COMPANY_NAME]) total,'Range:50-100000K' from (

select sum(amount) sums,COMPANY_NAME

from Xchange

where TNX_STAT_CODE=4  and [INP_USER_ID]>0

AND  appl_date >=(select dateadd(month,datediff(month,0,getdate())-12,0)) AND appl_date <=(select getdate())

and To_CCY='USD'

group by COMPANY_NAME

having sum(amount)>=50001 and sum(amount)<=100000)l2

union all

select count( distinct [COMPANY_NAME]) total,'Range:Above -100000K' from (

select sum(amount) sums,COMPANY_NAME

from Xchange

where TNX_STAT_CODE=4  and [INP_USER_ID]>0

AND  appl_date >=(select dateadd(month,datediff(month,0,getdate())-12,0)) AND appl_date <=(select getdate())

and To_CCY='USD'

group by COMPANY_NAME

having sum(amount)>=100001)l2

5.The sum of the amount should be grouped based on the company_name and then filtered based on the TNX_STAT_CODE =4 and the INP_USR_ID should be grater than 0 and not blank. Also we will the apply a date filter ie relative date filter for the last 12 calender month based on APPL_DATE.

6.Action taken: Have created a measure :

Range Amount:= CALCULATE(SUM('FACT_CONFIG_DERIVED_GTP_PRODUCT'[AMOUNT]),ALLEXCEPT(FACT_CONFIG_DERIVED_GTP_PRODUCT,FACT_CONFIG_DERIVED_GTP_PRODUCT[COMPANY_NAME]))

7.Then created a column as below:

Value Range=

SWITCH (

    TRUE (),

    FACT_CONFIG_DERIVED_GTP_PRODUCT[Range Amount] >= 0

        && FACT_CONFIG_DERIVED_GTP_PRODUCT[Range Amount]<= 50000, "0-50K",

     FACT_CONFIG_DERIVED_GTP_PRODUCT[Range Amount]  >= 50000

        && FACT_CONFIG_DERIVED_GTP_PRODUCT[Range Amount] <= 100000, "51K-100K",

     FACT_CONFIG_DERIVED_GTP_PRODUCT[Range Amount]  >= 100000, "Above 101K"

                )

8.Then used value Range on X axis and then company name on the value (Count Distinct)

9.The result is not as expected seeing few records having less count coming in wrong range bucket.