Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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
Cherry, I figured this out, thank you for the reply.
Carly
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
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 |
User | Count |
---|---|
88 | |
84 | |
67 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |