topic Re: Need to get subtotals for States and Cities in DAX Commands and Tips

Need to get subtotals for States and Cities

daxlearner — Sat, 07 Jan 2023 21:58:19 GMT

Hi Experts,

Please help me with Dax measure, need a Subtotals for States and cities individually

Used

Total Sales = Calculate(SUM(table(sales),Allselected(table),Removefilters(Table(Cities))

With this im able to get answer if i filter for individual state and all cities belong to that state, But when i select more than 2 states getting total sum of those states,But my goal to get individual total sales like below snip

Re: Need to get subtotals for States and Cities

Greg_Deckler — Sun, 08 Jan 2023 01:12:32 GMT

daxlearner Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Need to get subtotals for States and Cities

daxlearner — Sun, 08 Jan 2023 04:34:48 GMT

Greg_Deckler ,Thank you very much for responding to my post.Please check details below

State	City	Sales	Actual Total Sales	Expected Total Sales
California	San Francisco	400	1000	600
California	Los Angeles	200	1000	600
Florida	Tampa	300	1000	400
Florida	Tampa	100	1000	400

Re: Need to get subtotals for States and Cities

tamerj1 — Sun, 08 Jan 2023 15:32:17 GMT

Hi daxlearner
Please try

Total Sales = CALCULATE ( SUM ( 'Table'[sales] ), ALLEXCEPT ( 'Table', 'Table'[State] ) )

Re: Need to get subtotals for States and Cities

daxlearner — Sun, 08 Jan 2023 18:26:23 GMT

Thank You for reply tamerj1 ,that measure gives Total States Sales,my goal to get State wide total,Ie Field value total.
Could you Please check below Post.

Re: Need to get subtotals for States and Cities

tamerj1 — Sun, 08 Jan 2023 19:22:41 GMT

daxlearner

Not sure if I fully understand. Please try

Total Sales =
CALCULATE ( SUM ( 'Table'[sales] ), ALLSELECTED ( 'Table'[City] ) )

Re: Need to get subtotals for States and Cities

v-zhangti — Mon, 09 Jan 2023 06:11:58 GMT

Hi, daxlearner

Total Sales = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[State]))

Is this the outcome you expect? If not, please show the picture of your desired result.

Best Regards,

Community Support Team _Charlotte

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