calculate average of sum of calculated values per category

[Anonymous]

I have a dataset containing, among other things, IDs and Categories. Value is a calculated column, based on some other columns in this table. (didn't include it in this model, to avoid confusion.

I have made a little example of my data, to make explaining a little bit easier (numbers in front of rows are NOT part of the data!)

The first table is my data table, with my calculated Values column.

The Values column is based on:

IF(TRUE, CALCULATE(SUM(), ALLEXCEPT()), BLANK())

The second table is the next step. All values are summed up based on the ID-Cat combination. So rows 3 and 8 are summed up, and rows 5, 7, 11 are summed up. Rows 4, 10 11 are NOT summed up, because they have different IDs.

The third table comes next. This time, all values are summed up based on only the category. Rows 3 and 4 are summed up, rows 5 and 6 are summed up, and 7, 8, 9 are summed up (you get the rules?)

Now comes another tricky part. To be able to get to the fourth table, I need to calculate the averages of the values per category. By other means, it should take the sum of values and divide by the number of rows per category in table 2 (NOT TABLE 1).

I really struggle to find a way to do this, but I am very sure and dedicated to get this fixed 🙂

I currently have:

Avg = 

VAR TmpTable = SUMMARIZE(		//Creates table2
	table1,
	ID,
	Category,
	"TotalValues",
	CALCULATE(SUM(Value), ALLEXCEPT(ID))
)
RETURN

CALCULATE(//Average TmpTable per Category)

pbix file: Avg of Sum 

I hope anyone understands my problem 😉

Cheers

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[lbendlin]

In welchem Universum ist 170+150=290 ?  Und warum ist EE-4 270 und nicht 150?

[Anonymous]

My example indeed wasn't all correct. I'll change it to a new one.

EE4 should actually be none of those. It should be 160 because it is part of category 4. Avg stands for the average of that category.

This also means BB3 should be 180 for that same reason.

I've also uploaded a little example pbix file.

[v-lionel-msft]

Hi @Anonymous ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Yay, it's working!

Although, is there any way to make it all fit into one single DAX formula??? Because now I have all these columns that don't get used.

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

If you use this method, you could only merge formulas like this, the other columns in the calculation table can no longer be reduced.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.