topic Re: AVERAGEX after using IF Statement in Power BI in DAX Commands and Tips

AVERAGEX after using IF Statement in Power BI

Itachi_Utchiha — Mon, 30 Oct 2023 16:06:48 GMT

Hi everyone !

Please, I have this Problem and I need your help to solve it 🙂

I have Sales table with relation to Date Dim table , one filter is the year

this is pbix file ↓

I calculated the Current year Sales and Current year Quantity and average Price of current year and the totals are good

CYSALES = (SUM(Sales[netValue]))

CYQTY = (SUM(Sales[Qty]))

AVG Price CYSales = AVERAGEX(SUMMARIZE(Sales,Sales[CustomerNumber ],Sales[PartNumber]),[CYSALES]/[CYQTY])

with the same logic I calculated the Previous year Sales and Previous year Quantity and average Price of previous year and the totals are good

PYQTY // Previous year Quantity

PYSALES // Previous year sales

AVG Price PYSales =PYSALES /PYQTY

I have a condition for AVG Price CYSales, IF (AVG Price CYSales) is blank replace it by AVG Price PYSales // if current year value is blank replace it by the previous year value

and for AVG Price PYSales , IF (AVG Price PYSales) is blank replace it by AVG Price CYSales // if previous year value is blank replace it by the current year value

FOR THIS ,I created two new measures :

the result on the report :

but I want the Averagex totals correct !!!! like on Excel file

Please I need your help !!

Re: AVERAGEX after using IF Statement in Power BI

scee07 — Mon, 30 Oct 2023 06:20:41 GMT

Hi,
here is the workaround (this shows up in muliple other posts).
Let's assume a table

Col is thie the original numbers. As soon as blanks come into play, the total of the Col columns is not shown, although formatted as number and not even with blanks. When I wrap the column everything is fine.

To be honest, I refuse to think about, why this is happening. I just apply the workaround for my conditional average as well:

So, lesson: if the visual is showing unintuitive stuff, try to find wrapper measures, where you control the outcome in the rows and in the totals.

Hope this helps.
Christian

Re: AVERAGEX after using IF Statement in Power BI

Anonymous — Tue, 31 Oct 2023 14:54:34 GMT

Hi scee07 , thank you so much for your response !

I tried it but still not working !! I attached the Pbix file to the question ! I'll be so grateful if you can help !

Regards .

Re: AVERAGEX after using IF Statement in Power BI

Anonymous — Tue, 31 Oct 2023 21:00:46 GMT

Greg_Deckler , Please can you help us in this issue ? thank you in advance

Re: AVERAGEX after using IF Statement in Power BI

scee07 — Wed, 01 Nov 2023 12:40:06 GMT

Hi,
I will do some calculated columsn for my solution proposal. This can always be translated to a complete measure approach as need be. I will pick one of your measures to explain my approach.

First of all, if you calculate averages just make sure what you want. Your example shows the average of the prices. This is different to the weighted average price (Total Net Value)/ (Total Units). If this is what you want, fine.

The other issue is that if you use summarize in your calculation, definitely all combinations that have no sales in the filtered year will not be there. So the combination year/product/part will have no context for the total and is missing.

If this is true, all combination where want to have values must be created:

SalesTmp = CROSSJOIN ( VALUES ( dateDim[year] ), VALUES ( Sales[CustomerNumber ] ), VALUES ( Sales[PartNumber] ) )

Then we do calculated columns to replace blanks with values.

TotalNetValue = VAR thisYear = SalesTmp[year] VAR thisCustomer = SalesTmp[CustomerNumber ] VAR thisPart = SalesTmp[PartNumber] VAR prevYear = thisYear - 1 VAR nxtYear = thisYear + 1 VAR netValueThisYear = CALCULATE ( SUM ( Sales[netValue] ), FILTER ( Sales, YEAR ( Sales[Invoice date] ) = thisYear && Sales[CustomerNumber ] = thisCustomer && Sales[PartNumber] = thisPart ) ) VAR netValueLastYear = CALCULATE ( SUM ( Sales[netValue] ), FILTER ( Sales, YEAR ( Sales[Invoice date] ) = prevYear && Sales[CustomerNumber ] = thisCustomer && Sales[PartNumber] = thisPart ) ) VAR netValueNextYear = CALCULATE ( SUM ( Sales[netValue] ), FILTER ( Sales, YEAR ( Sales[Invoice date] ) = nxtYear && Sales[CustomerNumber ] = thisCustomer && Sales[PartNumber] = thisPart ) ) RETURN IF ( NOT ISBLANK ( netValueThisYear ), netValueThisYear, IF ( NOT ISBLANK ( netValueLastYear ), netValueLastYear, netValueNextYear ) ) TotalQty = VAR thisYear = SalesTmp[year] VAR thisCustomer = SalesTmp[CustomerNumber ] VAR thisPart = SalesTmp[PartNumber] VAR prevYear = thisYear - 1 VAR nxtYear = thisYear + 1 VAR netValueThisYear = CALCULATE ( SUM ( Sales[Qty] ), FILTER ( Sales, YEAR ( Sales[Invoice date] ) = thisYear && Sales[CustomerNumber ] = thisCustomer && Sales[PartNumber] = thisPart ) ) VAR netValueLastYear = CALCULATE ( SUM ( Sales[Qty] ), FILTER ( Sales, YEAR ( Sales[Invoice date] ) = prevYear && Sales[CustomerNumber ] = thisCustomer && Sales[PartNumber] = thisPart ) ) VAR netValueNextYear = CALCULATE ( SUM ( Sales[Qty] ), FILTER ( Sales, YEAR ( Sales[Invoice date] ) = nxtYear && Sales[CustomerNumber ] = thisCustomer && Sales[PartNumber] = thisPart ) ) RETURN IF ( NOT ISBLANK ( netValueThisYear ), netValueThisYear, IF ( NOT ISBLANK ( netValueLastYear ), netValueLastYear, netValueNextYear ) ) Price = SalesTmp[TotalNetValue] / SalesTmp[TotalQty]

Here, you replacement logic should be incorporated. Might be that your rules are different. This is to check.
You now have the price per line.

Note that you have a full table without blanks here.
As mentioned above, you can take the average in two different ways:

WeightedPrice = [TotalNetValueM] / [TotalQtyM] PriceM = AVERAGE ( SalesTmp[Price] )

Here, your average requirement is satisfied (PriceM measure).
Some remarks:
- you have presumably read that calculated columns are bad and to be avoided. If you have big tables and not enough memory space, this might be true.

- however, there is no free lunch. Here, some calculations are done before visualisation, which means that the visual rendering will be quicker. The resulting measures above are super simple.
- There are lot of DAX commands that do some filter context artistery. If it is getting too complex, I have my problems debugging that. The more gifted might be OK with that.

- As you have seen yourself complex depenencies of measures and filters can get very confusing.

What at least I learned from all that:
- if the model is small., I do calculated columns. In this manner I have control over immediate steps and I can see better what is going on

- If needed, this can be translated into measures

All a matter of taste and depending on the specifics of the situation.

Best regards

Christian