Forum Discussion
Groupings
Hi,
I'm trying to find out the number of companies that had sales in particular states so i can plot the 2 groupings in the dashboard. The 2 grouping of states that i'm interested are (WA and VIC only) and (Others - all states) - see column in yellow.
I tried filtering but it doesn't work. I suspect some logic or if statement is required. For example,
- if sales in WA and VIC, new field called WA and VIC populated in column H
- if any other states inc WA and VIC, new field called Others populated in column H
Using the example below, if we work the figures out manually the number of companies with the above criteria:
WA and Vic (in grey) = 3
Others (no fill) = 1
There will be approx 20,000 records hence the need to recode the group via formulas or functions.
What is the best way of grouping the states. Do i use the group by function via power query, measurements at the dashboard section, or other functions? Many thanks in advance.
Hi @PowerUser123 ，
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a calculated column as below:
Grouped delivery state = VAR _companycount = CALCULATE ( COUNT ( 'Table'[Company ID] ), FILTER ( 'Table', 'Table'[Company ID] = EARLIER ( 'Table'[Company ID] ) ) ) VAR _statecount = CALCULATE ( COUNT ( 'Table'[Company ID] ), FILTER ( 'Table', 'Table'[Company ID] = EARLIER ( 'Table'[Company ID] ) && 'Table'[Delivery state] IN { "WA", "VIC" } ) ) RETURN IF ( _companycount = _statecount, "WA and VIC", "Others" )If the above one can't help you get the desired result, please provide some sample data with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
How to upload PBI in Community
Best Regards
4 Replies
You could add a conditional column in Power Query,
if delivery state = "WA" then "WA and VIC"
else if delivery state = "VIC" then "WA and VIC"
else "Others"
Thanks John for the steps but not quite there unfortunately.
After the recoding, company ABC records for WA and VIC have been recorded as "WA and VIC" whereas records for NSW and VIC have been recorded as "Others". Is there a way to recode based on this criteria:
"if company has sales outside of WA and VIC eventhough they have sales in WA and VIC, recode company delivery as Others"
Hi @PowerUser123 ，
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a calculated column as below:
Grouped delivery state = VAR _companycount = CALCULATE ( COUNT ( 'Table'[Company ID] ), FILTER ( 'Table', 'Table'[Company ID] = EARLIER ( 'Table'[Company ID] ) ) ) VAR _statecount = CALCULATE ( COUNT ( 'Table'[Company ID] ), FILTER ( 'Table', 'Table'[Company ID] = EARLIER ( 'Table'[Company ID] ) && 'Table'[Delivery state] IN { "WA", "VIC" } ) ) RETURN IF ( _companycount = _statecount, "WA and VIC", "Others" )If the above one can't help you get the desired result, please provide some sample data with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
How to upload PBI in Community
Best Regards
I think you'd need to add a calculated column in DAX, something like
New column = var allStates = CALCULATETABLE( VALUES('Table'[Delivery state]), ALLEXCEPT('Table'[Company])) return IF( ISEMPTY( EXCEPT( allStates, { "WA", "VIC"})), "WA and Vic", "Others")
