IF then plus Groups in Direct Query

[sotoc]

Hi there,

Data located here:

https://www.dropbox.com/s/sgsefrujnjkzcai/CarlySoto_data2.csv?dl=0

I am using Direct Query and have to do all modeling in the report itself (can't do any steps in Query Editor).

I've got customers with two types of sales (date type whole number) in two different fields, plus 2 fields with their descriptors.

Individual and HQ -- sales for the single site *this is what I need
Consolidated -- sales for all stores in their corporation *this is what I want to omit

Sometimes the Individual site/HQ = corporation so the same sales amount is reported in both fields. Whichever Desc = Individual or HQ, the other is always Consolidated.

I want to use only Individual OR HQ sales for each customer (whichever is present):
If Desc0 = Individual or HQ use Num0
If Desc1 = Individual or HQ use Num1

Some customers have blanks

Once this is calculated I want to put the counts into bands into a matrix and/or simple bar/pie charts.

Thank you!

Carly

[v-piga-msft]

Hi @sotoc,

I'm still a little confused about your desired output.

What is Sales Volume?

Do you want to calculate the sales? If it is, what is the logic?

Based on your data sample, I'm not clear which could be calculated as sales. Numx?

Please describe your requirement in more details.

Best  Regards,

Cherry

[sotoc]

Cherry, I figured this out, thank you for the reply.

Carly

[v-piga-msft]

Hi @sotoc，

It's glad that you have solved your problem.

If it is convenient, ccould you share your solution or accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best Regards,

Cherry

[sotoc]

Hi Cherry @v-piga-msft, I am still having trouble. Here is a new sample file and hopefully a better description of what I am trying to do in terms of creating custom columns in the report view (cannot use the Power Queries Editor). 

1. Create a NewColumnCount to show EITHER Employees1Count or Employees2Count IF either Employees1Type or Employees2Type = Sub1 or Sub2 or Sub3. These Sub types will never occur on the same row, each CompanyID will have either 1)  Sub1, Sub2, Sub3, OR 2) "ALL", OR 3) NULL.

-- If "ALL" occurs in one EmployeesType and the other EmployeesType column is populated with Sub1, Sub2, or Sub3, ignore "ALL" and take the Sub for NewColumnCount.

-- If either Type column is "All" and the other Type is NULL, use the EmployeeCount for "ALL".

-- If both columns are NULL, use 0 in NewColumnCount

2. Create a second new column, NewColumnRange, to define buckets of counts via ranges.

"0-1,000" = 0-999 quantity in NewColumnCount
"1,000-10,000" = 1,000-9,999 quantity in NewColumnCount
"10,000-100,000" = 10,000-99,999 quantity in NewColumnCount
">100,000" = 100,000+ quantity in NewColumnCount

3. Sort the NewColumnCount by NewColumnRange (ascending from lowest range to highest range), so that in a matrix or a visual's legend will show them in order of NewColumnRange above, not in ABC order or the counts in NewColumnCount.

I am quite confused with creating columns with calculate, if/IF, filter, and on top of that adding sort order, all in the Report view!
Thanks again for taking a look.

Sample Data (last 3 columns are the desired output columns):

CompanyID	Employees1Type	Employees1Count	Employees2Type	Employees2Count	Desired NewColumnCount	Desired NewColumnRange	Desired SortOrder
800	Sub1	1,700	All	454,000	1,700	0-1,000	1
876	Sub3	1,900	All	454,000	1,900	1,000-10,000	2
494	All	168,085	Sub1	152,085	152,085	>100,000	4
100	All	183,055	Sub2	144,000	144,000	>100,000	4
996	All	140,800	NULL	0	140,800	>100,000	4
536	All	8,045	NULL	0	8,045	1,000-10,000	2
231	Sub2	994,010	NULL	0	994,010	>100,000	4
232	Sub3	690	NULL	0	690	0-1,000	1