Want to sum up Item ID values based on Category Id and Sub-category Id and show it axis of bar chart

[AshDil]

Hi,

I have data as follows:

Category ID	Sub-Category ID	ITEM ID	Sales
1001	100101	2	1000
1002	100201	2	2000
1003	100301	8	3000
1001	100102	2	1200
1001	100101	8	2000
1002	100201	18	1500

Now I want Item ID need to be sum up by Category ID and Sub-Category ID, the resultant Item ID need to be used as axis in column chart.

Eg: Category Id = 1001, Sub-category id = 100101 has two Item Id's = 2 & 8 (the resultant item id is sum of 2 and 8 = 10) and Sales Value = 3000

Now I need Item id = 10 to be used in "axis field" and sum of Sales = 3000 in "values field" of column chart. Same need to follow with other Category and Sub-category id's

Note: we don't use Category ID and Sub category Id in column chart.

Please help me to do it.

Thanks,

AshDil

[Jihwan_Kim · Accepted solution]

Hi,

One of ways to solve this is to create a new table like below.

Please check the below picture and the attached pbix file.

New Table =
GROUPBY (
    Data,
    Data[Category ID],
    Data[Sub-Category ID],
    "@AxisItemID", SUMX ( CURRENTGROUP (), Data[ITEM ID] ),
    "@SumSales", SUMX ( CURRENTGROUP (), Data[Sales] )
)

[amitchandak]

@AshDil , A new column

Sumx(Filter(Table, [Category Id] = earlier([Category Id]) &&  [Sub-category id] = earlier([Sub-category id]) ), [Item ID] )