Is it possibly to display item total by using sumx in clustered chart

[Maxemus2000]

Hi All,

I would like to display a sum of each calculated item from all of the stores in a clustered bar chart. Is it possible?

I have created a measure call "Impact" to calculate each item impact from each store. When I put Impact measure in a matrix table with store and item, it displays each item impact corretly. Below is a code from Impact measure. 

Impact = 
VAR StoreVol_2020 = CALCULATE(SUM(Sales[Amount]),
                            'Calendar'[Year] = 2020,
                            'ID'[ID] = 1)
VAR StoreVol_2019 = CALCULATE(SUM(Sales[Amount]),
                            'Calendar'[Year] = 2019,
                            'ID'[ID] = 1)
VAR AllStoreVol_2020 = CALCULATE(SUM(Sales[Amount]),
                            'Calendar'[Year] = 2020,
                            'ID'[ID] = 1,
                            ALLSELECTED(Sales[Item]))
VAR AllStoreVol_2019 = CALCULATE(SUM(Sales[Amount]),
                            'Calendar'[Year] = 2019,
                            'ID'[ID] = 1,
                            ALLSELECTED(Sales[Item]))
VAR StoreCost_2020 = CALCULATE(SUM(Sales[Amount]),
                            'Calendar'[Year] = 2020,
                            'ID'[ID] = 2)
VAR StoreCost_2019 = CALCULATE(SUM(Sales[Amount]),
                            'Calendar'[Year] = 2019,
                            'ID'[ID] = 2)
VAR AllStoreCost_2020 = CALCULATE(SUM(Sales[Amount]),
                            'Calendar'[Year] = 2020,
                            'ID'[ID] = 2,
                            ALLSELECTED(Sales[Item]))
VAR AllStoreCost_2019 = CALCULATE(SUM(Sales[Amount]),
                            'Calendar'[Year] = 2019,
                            'ID'[ID] = 2,
                            ALLSELECTED(Sales[Item]))
VAR AvgCost_2020 = DIVIDE(StoreCost_2020, StoreVol_2020)
VAR AvgCost_2019 = DIVIDE(StoreCost_2019, StoreVol_2019)
VAR AllStoreAvgCost_2020 = DIVIDE(AllStoreCost_2020, AllStoreVol_2020)
VAR AllStoreAvgCost_2019 = DIVIDE(AllStoreCost_2019, AllStoreVol_2019)
VAR StoreVolPct_2020 = DIVIDE(StoreVol_2020, AllStoreVol_2020)
VAR StoreVolPct_2019 = DIVIDE(StoreVol_2019, AllStoreVol_2019)
VAR PctDiff = StoreVolPct_2020 - StoreVolPct_2019
VAR VolImpact = PctDiff * AllStoreVol_2020
VAR CostImpact = AvgCost_2019 - AllStoreAvgCost_2019
VAR Impact = VolImpact * CostImpact
Return
Impact

Data in Sales table

Here is relationship between each table. 

Here is a matrix table showing calculated impact from each item in each store. When I put item in a clustered bar with Impact measure, it shows nothing.

My end goal is to get a clusterd bar chart that display a sum Impact measure by each item. 

Appreicate all of you guys help!

[lbendlin]

Show the formula for SUMX_Total.

[Maxemus2000]

@lbendlin Thanks for taking a look at this! 

Here is a formula for SUMX_Total

SUMX_Total = sumx(VALUES(Sales[Store]),sumx(VALUES(Sales[Item]),[Impact]))

Also, i think x, y, and z might re-do their "Impact" measure calculations in the clustered chart since we don't have store in there. If that's true, I need to find to way to get the result as i stated in my end goal. 

[v-xuding-msft]

Hi @Maxemus2000 ,

Have you resolved it? If not, can you please share a dummy file with us? You could upload it into Onedrive for business. Then we will understand clearly. Thanks!

[Maxemus2000]

@v-xuding-msft 

Due to IT security, I don't think you will be able to access our sharedrive even though i provide the link to the model. 

My dummy model is the exactly what i posted in my original question. I only have 2 measures; Impact and SUMX_Total. Both of their code are posted. 

Sales table is a fact table. 

Relationship:
ID table to Sales, 1 to many via ID
Item table to Sales, 1 to many via Item
Calendar table to Sales, 1 to many via Year 
Store table to Sales, 1 to many via Store

[Maxemus2020]

@v-xuding-msft 

Here is a link to my sample file

Sample File 

[v-xuding-msft · Accepted solution]

Hi @Maxemus2020 , @Maxemus2000 ,

Please try this:

Measure = SUMX(VALUES('Store'[Store]),[Impact])

 

[Maxemus2000]

@v-xuding-msft 

It works! Thanks for your help. I guess my previous SUMX was incorrect. 

SUMX_Total = sumx(VALUES(Sales[Store]),sumx(VALUES(Sales[Item]),[Impact]))

I'm still new to this, can you explain what went wrong with my SUMX above and why it didn;t give me the result I wanted? 

[lbendlin]

For debugging use lots of intermediate variables and CONCATENATEX for the debug output.