Help with incorrect totals

[Anonymous]

Hi All,

I have a measure that works fine at site level:

PerformanceSitesTotals = IF(HASONEFILTER('vw_ePos_SalesBySalesItemByDay'[outletId]),[PerformanceSites],SUMX(VALUES('vw_ePos_SalesBySalesItemByDay'[outletId]),[PerformanceSites]))

If i chart this on a line graph by week, it displays perfectly. My issue arises when i create a new graph with the totals in - it creates a new sum ignoring the weekly filter - is it possible to include an additional criteria [Year_Week] ?

[v-zhangti]

Hi, @Anonymous 

Can you provide sample data for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures or Excel. I look forward to your response.

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.

[Anonymous]

Hi v-zhangti, thanks for tracking back...

What i've done is create some measures to split out whats driving the performance in sales:

New Sites (no YoY data)

Price

Volume (units sold)

Mix (Change in high/low value categories)

Closed Sites (Sales LY and not TY)

The resulting grid works perfectly as shown below - this site was new for the first 15 weeks of this financial year, but then must have opened in week 16 last year, giving me some YoY data:

The issue arises when i drop the weekly split as I want to show the total impact, as it doesn't count the site as 'new' anymore, because over the period in question, the site does have some YoY data:

Here's my measures. First 'new sites' - simply counts if the site is new or not and multiplies sales by 1 if it is.

PerformanceSites = ([Ordering Sites] - [Ordering Sites LY]) * ([GrossSalesM] / [Ordering Sites])

Then i use the following to get it to total correctly at site level (This is the measure used on the chart for 'new sites'):

PerformanceSitesTotals = SUMX(VALUES('vw_ePos_SalesBySalesItemByDay'[outletId]),[PerformanceSites])

The other formulas work similarly, but if you're able to fix the one formula I can apply the same logic to the others

Thanks in advance for your help everyone!

[Greg_Deckler]

@Anonymous First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[amitchandak]

@Anonymous , Simply try like

SUMX(VALUES('vw_ePos_SalesBySalesItemByDay'[outletId]),[PerformanceSites])

[Anonymous]

Hiya,

Same result as my current formula. What's happpening is that I have a certain site that opens in the last week of a 15 week period i'm looking at, but then it's treated as an existing site showing massive growth, rather than a new site - which is what that measure totals.

The 'by week' line graph shows the correct values, but when i drop the weekly filter, it shows incorrectly