Range Creation with filter and all except

[Anonymous]

Hi Team,

I was trying to create a bar chart with the range on X axis.

The SQl query 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

I would want the range on the x axis and Total as value. 

If the sum of the amount is less that 50k it should fall on range 0-50k, else if its greater than 50001 and less that 1lk it should fall in bucket 2, else in bucket above 1lk.

[dax · Accepted solution]

Hi @Anonymous , 

You could remove this in filter and try to refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Approach taken by me is : 

Created one measure : 

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

Then created one column : 

=
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"

)

Then on the visual took value range column on the axis and company_name on value n did count distinct, but this is not fectching me expected result.

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

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.

[Anonymous]

COMPANY_NAME	TNX_STAT_CODE	INP_USR_ID	AMOUNT	APPL_DATE	To_CCY
Apple	1	0	35422	28/09/2020	GBP
Airtel	4	546	5000	28/03/2020	USD
Mango	4	34612	947383	28/02/2020	USD
Idea	4	356	33	28/01/2020	USD
Apple	4	85	60000	28/09/2019	USD
Airtel	5		899987	28/01/2019
Idea	4	34445	45	28/09/2019	USD
Mango	4	98	4567393	8/03/2020	USD
Airtel	4	90	5000	2/04/2020	USD

Expecting: 0-50k bucket to have count 2, 50001-100k have count 1 and above 100k as count 1

[dax]

Hi @Anonymous , 

You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi, 

Thank you so much for the help this is giving me exactly what i was expecting.

But i have one issue, the TO_CCY should not be included in the filter, i have a slicer with multiple currency when i select them the bucket should filter accordingly.

is that possible?

@dax 

[dax · Accepted solution]

Hi @Anonymous , 

I think you need to use [TO_CCY]  from summarize table(my Table 2) in slicer  instead of from fact table. If you want to use this field from fact table(Table(2) in my sample), I think you need to create relationship between two tables based on [TO_CCY] .

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dax · Accepted solution]

Hi @Anonymous , 

You could remove this in filter and try to refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @dax 

I did try that and got an output , this output should change when i apply the currency filter but i see that it doesnt change. 

When i used USD in the DAX it showed count 23 when i remove it the count was 25.

I used TO_CCY from FACT table as slicer and selected USD but this dint change from 25 to 23.

Thanks in advance