Home

Distinct row count not matching grand total in PowerPivot

%3CLINGO-SUB%20id%3D%22lingo-sub-815615%22%20slang%3D%22en-US%22%3EDistinct%20row%20count%20not%20matching%20grand%20total%20in%20PowerPivot%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-815615%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20Power%20Query%20under%20%22Get%20%26amp%3B%20Transform%22%20to%20work%20with%20a%20large%20dataset%20(1.4M%20rows).%20I%20am%20getting%20stuck%20when%20trying%20to%20add%20a%20conditional%20column%20that%20breaks%20down%20the%20data%20by%20category%20using%20greater%20than%20or%20less%20than%20statements.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAn%20example%20of%20the%20categories%20I%20wish%20to%20create%20are%20%22%26gt%3B100%22%2C%20%22%26gt%3B%3D%2075%22%2C%20%22%26gt%3B%3D%2050%22%2C%20%22%26lt%3B50%22%2C%20etc.%20I%20am%20able%20to%20do%20this%20with%20the%20conditional%20column%20tool%20in%20the%20%22Power%20Query%20Editor%22%2C%20the%20problem%20I%20run%20into%20is%20when%20I%20view%20the%20data%20in%20PowerPivot.%20The%20data%20from%20the%20statement%20above%20looks%20correct%20in%20the%20Power%20Query%20Editor%2C%20but%20is%20skewed%20in%20the%20PowerPivot%20table%20-%20where%20the%20row%20totals%20for%20a%20distinct%20count%20do%20not%20equal%20the%20distinct%20count%20under%20the%20grand%20total.%20I%20am%20guessing%20that%20a%20unique%20number%20that%20fits%20both%20%26gt%3B%3D50%20and%20%26gt%3B%3D%20100%20are%20being%20double%20counted%2C%20but%20I%20have%20no%20idea%20how%20to%20correct%20this.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20researching%20and%20have%20found%20mixed%20answers%2C%20no%20solution%20yet.%20Any%20suggestions%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-815615%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
excelgeek
Occasional Contributor

Hello - 

 

I am using Power Query under "Get & Transform" to work with a large dataset (1.4M rows). I am getting stuck when trying to add a conditional column that breaks down the data by category using greater than or less than statements.

 

An example of the categories I wish to create are ">100", ">= 75", ">= 50", "<50", etc. I am able to do this with the conditional column tool in the "Power Query Editor", the problem I run into is when I view the data in PowerPivot. The data from the statement above looks correct in the Power Query Editor, but is skewed in the PowerPivot table - where the row totals for a distinct count do not equal the distinct count under the grand total. I am guessing that a unique number that fits both >=50 and >= 100 are being double counted, but I have no idea how to correct this. 

 

I have been researching and have found mixed answers, no solution yet. Any suggestions? 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies