How to get the Total and Subtotal without Average Calculation

[majo23]

Hi everyone,

So Im doing some measures in DAX, but for the total it shows me the average and not the total of each class (and unit)

I thing the problem is that Im using AVERAGE in the measure, but I dont know how to get the number for each without sum it (I was trying to used something like VLOOKUP) but that only works for columns measure Example Avegare.pbix

In the original report Im also having the same problem with the Revenue measure 

The calculation that Im using for the Revenue is 

Revenue = CALCULATE(AVERAGE('Table'[Cut]) / 5 * COUNT('Table'[Units]))

For the Class part, I need to sum all the measures that I get in the Packages, but because I have differents Cut and Rate the calculations doesnt work 

If someone have any suggestion, I would appreciated

MJ

[LukeVanLaar26]

@majo23 

Try these:

Rate = 
VAR _Packages = VALUES('Table'[PackageID])
RETURN
SUMX(_Packages,CALCULATE(AVERAGE('Table'[Rate])))

Revenue = 
VAR _Packages = VALUES('Table'[PackageID])
RETURN
SUMX(_Packages,CALCULATE(DIVIDE(AVERAGE('Table'[Cut]), 5) * COUNT('Table'[Units])))

Let me know if that isn't what you were looking for.

Connect on LinkedIn

[majo23]

I get the same result 

[LukeVanLaar26]

@majo23 

In your first post you were showing Class at the top of the hierarchy and indicated you wanted to calculate at the package level and sum to the class level. In this latest post you are showing Package at the top and are indicating you want to calculate at the Unit and roll up to the above levels:

Modify the measures I gave before to this (swapping Package for Units):

Rate Measure = 
VAR _Units = VALUES('Table'[Units])
RETURN
SUMX(_Units,CALCULATE(AVERAGE('Table'[Rate])))

Is that what you are looking for?