Forum Discussion
Unique Values based on Different Filters
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.
2 Replies
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