Forum Discussion
Range Creation with filter and all except
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.
Hi Anonymous ,
You could remove this in filter and try to refer to my sample for details.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
- Greg_Deckler
Community Champion
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.- We need t create a bar chart with range in the Axis and the distinct count of the company_name on the value field.
- The range should have three buckets
- 0-50k
- 50001k-100k
- Above 100k
- So, we need to count the companies that have made transactions in these range buckets.
- 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.
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
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.
