Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I am struggling to find the out the unique values based on the different filters. Here below is my sample data table and I want to find out the Associate name based on the highest spend by Division By Subcategory. For below table the expected one answer would be like below.
I want the same figures for from the data
Expected Result: I want all details
Associate Name | Sub Category | Division |
C1 | Group | Road |
A1 | Labour | Utilities |
A1 | Waste | Construction |
Data Table
Sub Category | Division | Associate Name | Total Spend |
Labour | Utilities | A1 | 8 |
Labour | Utilities | B1 | 15 |
Labour | Utilities | C1 | 12 |
Labour | Utilities | D2 | 9 |
Labour | Utilities | A1 | 45 |
Hardware | Engineering | C1 | 31 |
Hardware | Engineering | D2 | 39 |
Hardware | Engineering | A1 | 42 |
Waste | Engineering | C1 | 2 |
Waste | Construction | D2 | 13 |
Waste | Construction | A1 | 28 |
Waste | Construction | A4 | 15 |
Work | Construction | A5 | 43 |
Work | Infrastructure | A1 | 8 |
Work | Infrastructure | C1 | 48 |
Work | Infrastructure | D2 | 21 |
Group | Infrastructure | A1 | 37 |
Group | Road | A1 | 19 |
Group | Road | C1 | 45 |
Group | Road | D2 | 19 |
Note: I am using direct query as data from source system.
Please help me.
Hi,
I am not getting the correct or unique value when I am adding all those columns in Table visualisation. Please find the attached spreadsheet. When I am adding sub-categories it is not showing the higest spend division, instead of it is showing Division and several subcategories. But answer should be the assoicate name have the highest spend in which subcategory and its respective division name i.e. Check given below example
Associate Name | Division | SubCategory | Spend |
A1 | Asset | labour | 1.4m |
Please find the results I got after applying DAX function you shared.
Associate Name | Division | SubCategory | Total spend |
P1 | Group | HIR | 871250 |
P1 | Services | HIR | 816872 |
P1 | Utilities | HIR | 507428 |
J1 | Asset | General | 368213 |
J1 | Utilities | General | 180877 |
R1 | Infrastructure | SAFETY | 150041 |
JJ | Mining | REMOVAL | 112983 |
TO | Asset | NIFORMS | 98109 |
RENTAL | E & C | General | 89769 |
DI | Services | WHOL | 79230 |
R1 | Services | SAFETY | 66901 |
JJ | Utilities | REMOVAL | 61828 |
DI | Mining | WHOL | 50426 |
MA | Utilities | SAFETY | 36554 |
TO | E & C | NIFORMS | 34771 |
PORTER | Infrastructure | HIR | 14487 |
JJ | Infrastructure | REMOVAL | 9276 |
JJ | Services | REMOVAL | 8675 |
RS | Infrastructure | ELECTRICAL | 7104 |
RS | Rolling | ELECTRICAL | 6408 |
TO | Infrastructure | NIFORMS | 4622 |
MA | E & C | SAFETY | 3358 |
DI | Group | WHOL | 2682 |
A1 | Asset | LABOUR | 1045049 |
STAFF | Mining | LABOUR | 714210 |
GTE | Infrastructure | LABOUR | 609906 |
POWER | Services | LABOUR | 518041 |
KI | Asset | HIR | 393785 |
EFFECTIVE | Group | LABOUR | 355823 |
RPM HIRE | Services | General | 346183 |
RO | Asset | ELECTRICAL | 287974 |
PETER | Utilities | LABOUR | 240067 |
KEN | Infrastructure | General | 232636 |
PILBARA | E & C | HIR | 177883 |
MID | Utilities | ELECTRICAL | 176948 |
MYELEC | E & C | ELECTRICAL | 169325 |
B1 | Services | ELECTRICAL | 133544 |
JR | Asset | REMOVAL | 115107 |
E A | Mining | ELECTRICAL | 66420 |
B2 | Utilities | WHOL | 61633 |
FLINDANE | Group | General | 35727 |
RWW | Services | NIFORMS | 30547 |
BU | Asset | SAFETY | 30315 |
THE | Mining | NIFORMS | 28214 |
B4 | Asset | WHOL | 25808 |
C1 | Rolling | SAFETY | 25259 |
SYSTEMS | Mining | SAFETY | 25020 |
MULTI | Mining | General | 20736 |
BUSB | Mining | HIR | 18786 |
HIP POCKET | Utilities | NIFORMS | 6425 |
DRS | Group | NIFORMS | 6179 |
MITRE 10 | Infrastructure | WHOL | 3600 |
ELECTRIC | Group | ELECTRICAL | 3547 |
RECYCLING | E & C | REMOVAL | 3160 |
AU | Rolling | NIFORMS | 2964 |
TMH | Group | SAFETY | 1681 |
'
Thanks
Uphar
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |