topic Re: Sum of an already existing average measure in DAX Commands and Tips

Sum of an already existing average measure

Anonymous — Thu, 17 Mar 2022 22:26:45 GMT

Hi PowerBI community and hopefully I'll find an aswer here. 😊

My problem is that I have a simple AVERAGE(X) function because I'm using a table has been cross joined (a cartesian product). Now, when drilling down the smalledt granularity possible (eg. product code), I get a correct result, but when drill up to category, I get an average inside of that category, which is expected and logical behaviour.

Is it possible to make a derived measure out of the simple AVERAGE(X) measure that would sum whenever I "climb up" a dimension, eg. from product to category. I tried doing the following which is kind of what I am looking (I think):

SUMX(VALUE(Y), AVERAGE(X))

View below wold show seperate inputted forecasts for some dates for the same product:

On the other hand, view below should show "total" (SUM) of each category:

If any of resident DAX experts could help, I would be very grateful 🙂

Thank you!

Re: Sum of an already existing average measure

Whitewater100 — Fri, 18 Mar 2022 00:39:38 GMT

Hi:

The INSCOPE function works well for your question. I'm not sure if you have subcat, but you can start with this approach. This should reveal the category % of total and thenhow the products reporting to this category contribute to their category. I hope it helps you.

SalesPct =

VAR AllSales =

CALCULATE ( [Total Sales], ALLSELECTED () )

VAR CategorySales =

CALCULATE ( [Total Sales], ALLSELECTED (), VALUES ( Products[Brand] ) )

VAR CurrentSales = [Total Sales]

RETURN

SWITCH (

TRUE (),

ISINSCOPE ( Products[Product] ), DIVIDE ( CurrentSales, CategorySales ),

ISINSCOPE ( 'Products'[Category] ), DIVIDE ( CurrentSales, AllSales )

)

The other more basic share one can be:

DIVIDE([Sales Amount],

CALCULATE([Sales Amount], REMOVEFILTERS(Products[Category])))

or ,REMOVEFILTERS())

Re: Sum of an already existing average measure

Anonymous — Sun, 20 Mar 2022 22:20:57 GMT

Hi there,

sorry for the lack of reply, just now got to it. I have to say, this honestly look a bit too complicated for me, but I have figured out (at least I think so) what to do:

SUMX(VALUES(Y), CALCULATE(MAX(X))

This seems to work for now, I'll report back if there any unwanted occurences.

Thank you again!

Re: Sum of an already existing average measure

Whitewater100 — Mon, 21 Mar 2022 00:28:47 GMT

That's great. If it is a solution can you please mark it as solved?

Have a good week ahead!