AVERAGEX after using IF Statement in Power BI

[Itachi_Utchiha]

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 !! 

[scee07]

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

[Anonymous]

Hi

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

[scee07]

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

[Anonymous]

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 .