Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 NameSub CategoryDivision
C1GroupRoad
A1LabourUtilities
A1Waste Construction

 

Data Table

 

Sub CategoryDivisionAssociate NameTotal Spend
LabourUtilitiesA18
LabourUtilitiesB115
LabourUtilitiesC112
LabourUtilitiesD29
LabourUtilitiesA145
HardwareEngineeringC131
HardwareEngineeringD239
HardwareEngineeringA142
WasteEngineeringC12
WasteConstructionD213
WasteConstructionA128
WasteConstructionA415
WorkConstructionA543
WorkInfrastructureA18
WorkInfrastructureC148
WorkInfrastructureD221
GroupInfrastructureA137
GroupRoadA119
GroupRoadC145
GroupRoadD219

Note: I am using direct query as data from source system.

Please help me.

 

2 REPLIES 2
Anonymous
Not applicable

File attached.

 

Best

D

Anonymous
Not applicable

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 NameDivisionSubCategorySpend
A1Assetlabour1.4m

 

Please find the results I got after applying DAX function you shared.

 

Associate NameDivisionSubCategoryTotal spend
P1GroupHIR871250
P1ServicesHIR816872
P1UtilitiesHIR507428
J1AssetGeneral368213
J1UtilitiesGeneral180877
R1InfrastructureSAFETY150041
JJMiningREMOVAL112983
TOAssetNIFORMS98109
 RENTALE & CGeneral89769
DIServicesWHOL79230
R1ServicesSAFETY66901
JJUtilitiesREMOVAL61828
DIMiningWHOL50426
MAUtilitiesSAFETY36554
TOE & CNIFORMS34771
PORTERInfrastructureHIR14487
JJInfrastructureREMOVAL9276
JJServicesREMOVAL8675
RSInfrastructureELECTRICAL7104
RSRollingELECTRICAL6408
TOInfrastructureNIFORMS4622
MAE & CSAFETY3358
DIGroupWHOL2682
A1AssetLABOUR1045049
STAFFMiningLABOUR714210
GTEInfrastructureLABOUR609906
POWERServicesLABOUR518041
KIAssetHIR393785
EFFECTIVEGroupLABOUR355823
RPM HIREServicesGeneral346183
ROAssetELECTRICAL287974
PETERUtilitiesLABOUR240067
KENInfrastructureGeneral232636
PILBARAE & CHIR177883
MIDUtilitiesELECTRICAL176948
MYELECE & CELECTRICAL169325
B1ServicesELECTRICAL133544
JRAssetREMOVAL115107
E AMiningELECTRICAL66420
B2UtilitiesWHOL61633
FLINDANEGroupGeneral35727
RWWServicesNIFORMS30547
BUAssetSAFETY30315
THEMiningNIFORMS28214
B4AssetWHOL25808
C1RollingSAFETY25259
SYSTEMSMiningSAFETY25020
MULTIMiningGeneral20736
BUSBMiningHIR18786
HIP POCKETUtilitiesNIFORMS6425
DRSGroupNIFORMS6179
MITRE 10InfrastructureWHOL3600
ELECTRICGroupELECTRICAL3547
RECYCLINGE & CREMOVAL3160
AURollingNIFORMS2964
TMHGroupSAFETY1681

'

Thanks 

Uphar

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors