IF then plus Groups in Direct Query

Question

Hi there,&nbsp;Data located here:https://www.dropbox.com/s/sgsefrujnjkzcai/CarlySoto_data2.csv?dl=0&nbsp;I am using Direct Query and have to do all modeling in the report itself (can't do any steps in Query Editor).&nbsp;I've got customers with two types of sales (date type whole number) in two different fields, plus 2 fields with their descriptors.&nbsp;Individual&nbsp;and HQ -- sales for the single site *this is what I needConsolidated -- sales for all stores in their corporation *this is what I want to omit&nbsp;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.&nbsp;I want to use only Individual OR HQ sales for each customer (whichever is present):If Desc0 = Individual or HQ use Num0If Desc1 = Individual or HQ use Num1Some customers have blanksOnce this is calculated I want to put the counts into bands into a matrix and/or simple bar/pie charts.&nbsp;&nbsp;Thank you!Carly

v-piga-msft · Answer

Hi&nbsp;sotoc,
&nbsp;
I'm still a little confused about your desired output.
&nbsp;
What is Sales Volume?
&nbsp;
Do you want to calculate the sales? If it is, what is the logic?
&nbsp;
Based on your data sample, I'm not clear which could be calculated as sales. Numx?
&nbsp;
Please describe your requirement in more details.
&nbsp;
&nbsp;
Best&nbsp; Regards,
Cherry

sotoc · Answer

Cherry, I figured this out, thank you for the reply.&nbsp;Carly

v-piga-msft · Answer

Hi sotoc，
&nbsp;
It's glad that you have solved your problem.
&nbsp;
If it is convenient, ccould you share your solution or&nbsp;accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
&nbsp;
Best Regards,
Cherry

sotoc · Answer

Hi Cherry&nbsp;v-piga-msft, I am still having trouble. Here is a new sample file and hopefully a&nbsp;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).&nbsp;&nbsp;1. Create a NewColumnCount to show&nbsp;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)&nbsp; Sub1, Sub2, Sub3, OR 2) "ALL", OR 3) NULL.&nbsp;-- 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&nbsp;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"&gt;100,000" = 100,000+ quantity in NewColumnCount&nbsp;3. Sort the NewColumnCount&nbsp;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.&nbsp;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.&nbsp;Sample Data (last 3 columns are the desired output columns):&nbsp;CompanyIDEmployees1TypeEmployees1CountEmployees2TypeEmployees2CountDesired NewColumnCountDesired NewColumnRangeDesired SortOrder800Sub11,700All454,0001,7000-1,0001876Sub31,900All454,0001,9001,000-10,0002494All168,085Sub1152,085152,085&gt;100,0004100All183,055Sub2144,000144,000&gt;100,0004996All140,800NULL0140,800&gt;100,0004536All8,045NULL08,0451,000-10,0002231Sub2994,010NULL0994,010&gt;100,0004232Sub3690NULL06900-1,0001

Forum Discussion

IF then plus Groups in Direct Query

4 Replies

Related content

merging direct query queries

RLS over Direct Query

Direct Query issue with Snowflake

History Data in Direct Query

Direct Query or Import