How would you summarise this table

[roshenrrujj]

hey guys, i have this data table that i need to summarise in Power BI DAX

Data:

Job	Contractor	Contractor Payment	State	Region	Town	Work Started	Work Ended
8439750	1086	$10,329.26	QLD	QLD Rural	Rockhampton	14/03/2023
8441239	1126	$82,135.70	QLD	QLD Rural	Rockhampton	1/03/2023
9909507	1148	$4,020.85	NSW	NSW Rural	Tweed Heads	3/05/2023
8438720	1198	$4,042.37	QLD	QLD Rural	Rockhampton	3/02/2023	30/03/2023
8438720	1198	$10,501.92	QLD	QLD Rural	Rockhampton	3/02/2023	30/03/2023
8439750	1198	$24,447.44	QLD	QLD Rural	Rockhampton	12/04/2023
8450245	1198	$199,771.46	NSW	NSW Rural	Tweed Heads	6/04/2023
8450432	1305	$43,029.86	TAS	TAS	Hobart	24/05/2023
8444572	1484	$253,608.70	QLD	QLD Rural	Rockhampton	16/02/2023
9906018	1649	$8,329.19	VIC	VIC Metro	Melbourne	8/03/2023	23/05/2023
8454213	1655	$75,492.70	VIC	VIC Rural	Albury/Wodonga	1/05/2023
60101349	1743	$913.68	VIC	VIC Metro	Melbourne	12/01/2023	12/01/2023
8452116	1824	$15,431.13	SA	SA	Adelaide	12/05/2023
8452263	1824	$23,402.84	WA	WA	Perth	5/05/2023
8442947	1832	$169,773.98	VIC	VIC Metro	Melbourne	25/01/2023
8453700	1832	$118,862.25	VIC	VIC Rural	Bendigo	27/02/2023
8445412	1869	$79,651.77	QLD	QLD Rural	Rockhampton	6/04/2023
9908698	1918	$2,904.14	QLD	QLD Metro	Brisbane	28/02/2023
9908698	1918	$1,920.60	QLD	QLD Metro	Brisbane	28/02/2023

how would you summarise that data in this way in DAX

Contractor	State	Avg Contractor Payment in State	The State overall average	Difference
1086	QLD	$                                             10,329.26	$                              52,171.32	80%
1126	QLD	$                                             82,135.70	$                              52,171.32	-57%
1148	NSW	$                                               4,020.85	$                            101,896.16	96%
1198	QLD	$                                             12,997.24	$                              52,171.32	75%
1198	NSW	$                                           199,771.46	$                            101,896.16	-96%
1305	TAS	$                                             43,029.86	$                              43,029.86	0%
1484	QLD	$                                           253,608.70	$                              52,171.32	-386%
1649	VIC	$                                               8,329.19	$                              74,674.36	89%
1655	VIC	$                                             75,492.70	$                              74,674.36	-1%
1743	VIC	$                                                   913.68	$                              74,674.36	99%
1824	SA	$                                             15,431.13	$                              15,431.13	0%
1824	WA	$                                             23,402.84	$                              23,402.84	0%
1832	VIC	$                                           144,318.12	$                              74,674.36	-93%
1869	QLD	$                                             79,651.77	$                              52,171.32	-53%
1918	QLD	$                                               2,412.37	$                              52,171.32	95%

[tamerj1 · Accepted solution]

Hi @roshenrrujj 
Please refer to attached sample file for the proposed solution

Table 2 = 
GENERATE ( 
    SUMMARIZE ( 
        'Table',
        'Table'[Contractor],
        'Table'[State]
    ),
    VAR AvgContractor = CALCULATE ( AVERAGE ( 'Table'[Contractor Payment] ) )
    VAR AvgState = CALCULATE ( AVERAGE ( 'Table'[Contractor Payment] ), ALLEXCEPT ( 'Table', 'Table'[State] ) )
    VAR PctDiff = DIVIDE ( AvgState - AvgContractor, AvgState )
    RETURN
        ROW ( "Avg Contractor Payment in State", AvgContractor, "The State overall average", AvgState, "Difference", PctDiff )
)

[tamerj1 · Accepted solution]

Hi @roshenrrujj 
Please refer to attached sample file for the proposed solution

Table 2 = 
GENERATE ( 
    SUMMARIZE ( 
        'Table',
        'Table'[Contractor],
        'Table'[State]
    ),
    VAR AvgContractor = CALCULATE ( AVERAGE ( 'Table'[Contractor Payment] ) )
    VAR AvgState = CALCULATE ( AVERAGE ( 'Table'[Contractor Payment] ), ALLEXCEPT ( 'Table', 'Table'[State] ) )
    VAR PctDiff = DIVIDE ( AvgState - AvgContractor, AvgState )
    RETURN
        ROW ( "Avg Contractor Payment in State", AvgContractor, "The State overall average", AvgState, "Difference", PctDiff )
)

[roshenrrujj]

thank you for that! Can i please ask:

- lets say i just need to use the state and contractor straight from the dataset (Rather than a DAX created table), and then have The State overall average, Avg Contractor Payment in State and difference as separate measures - would this be possible to implement? i tried your queries separate in measures and pulled them into a table visualisation, unfortunatley it did not work 😞

[tamerj1 · Accepted solution]

@roshenrrujj 

[roshenrrujj]

thanks so much! 🙏