All Column from rows

[ringovski]

Hi All,

Problem, I would like to have states grouped together as 'All" which is a sum of states and then seperately in the one matrix table. I have added a 'All' column to the dimension but this only works in a seperate matrix table. Is there a way to do this?

Required:

States	Value$
All	xxxx
NSW	xxxx
ACT	xxxx
SA	xxxx
WA	xxxx
NT	xxxx
QLD	xxxx
VIC	xxxx

I have a fact table a state dimension table, the state dimension has a row for each state and joins to the fact on the SK.

Table name Export_Fact_Codes_All
calendar_sk	country_sk	loading_sk	discharge_sk	product_code_sk	state_sk	quantity_sk	value$
300	100	1	600	121	1	400	1 328967
300	101	1	601	121	2	600	84353905
300	102	2	603	121	2	800	41839
301	100	1	600	121	2	900	12888454
301	101	2	601	121	3	1000	27880729
301	102	2	602	121	5	200	230237

Dim_State
state_sk	State_Code	State_Description	All_Description
1	NSW	New South Wales	All
2	QLD	Queensland	All
3	SA	South Australia	All
4	WA	Western Australia	All
5	NT	Northern Territory	All
6	TAS	Tasmania	All
7	VIC	Victoria	All
8	ACT	Australian Capital Territory	All

Thanks for any assistance.

[Anonymous]

Hi @ringovski ,

I can't understand it very well. Could you provide me with a .pbix file?

Best regards,
Community Support Team_ Scott Chang

[ringovski]

Thanks for the replies.
Client wants to be able to compare the sum of all states and each state at the same time in one visual, in this case a matrix table. I created the all column in the dimension table though this only works in a seperate matrix table.

Current (seperate tables)

All Column	Value$
All	xxxx

State	Value$
NSW	xxxx
VIC	xxxx
SA	xxxx
WA	xxxx
NT	xxxx
....

Desired (one table)

States	Value$
All	xxxx
NSW	xxxx
VIC	xxxx
SA	xxxx
WA	xxxx
NT	xxxx
....

Thanks

[Anonymous]

Hi @ringovski ,

There doesn't seem to be a way to add a row named ALL but Dax generates it automatically, I can provide you with a kind of workaround and you can check the result below:

Measure = var _t= ADDCOLUMNS('Table',"Sum",SUMX(FILTER(ALL('Table'),[ state_sk]=EARLIER([ state_sk])),[ value$]))
return SUMX(_t,[Sum])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[vanessafvg]

so are you saying you want a column for all totals? is that what you are asking?  How do you want to use this ALL?

If you are needing to create this on a column not as a measure as you have put above then you probably need to either aggregate all values on to one fact line in your fact at the grain with an all key that sits like a state in the dimension with a row like all, not as a seperate column

you cannot place all on the slicer like you have above if its not in the same column.