Still struggling with averages by a group in DAX

[Anonymous]

I have a table, lets says it has the following columns/data

Group1	Group2	Group3	Percent
1	A	i	5
1	B	i	10
1	C	i	15
2	A	i	20
2	B	i	25
2	C	i	30
3	A	i	40
3	B	i	45
3	C	i	50
1	A	ii	15
1	B	ii	30
1	C	ii	60
2	A	ii	20
2	B	ii	35
2	C	ii	65
3	A	ii	30
3	B	ii	45
3	C	ii	75

I want to solve for the average of Group 3 by Group 2, so the result would look something like this (if presented as a table):

Group2	Group3	Avg PCT
A	i	21.666
A	ii	21.666
B	i	26.666
B	ii	36.666
C	i	31.666
C	ii	66.666

Finally I want to multiply the AVG result by a [Measure] (seems like the easy part). I want to do this as a measure so that it is fully dynamic, ie, while my example here summarizes to Group2, you could just as likely calculate the overall average of Group3.

I've taken multiple approachs. I feel like it shouldn't be this hard, as usual I'm likely overlooking something basic with context. I thought I'd be able to use a CALCULATETABLE to create a virtual table, then AVERAGEX the result, but the numbers don't add up.

Thanks,

-R

[Anonymous]

// Actually, it's very easy with minimal coding.
// All you have to do is have one measure:

[Average] = AVERAGE( T[Percent] )

// and the rest is done through slicing
// on dimension attributes in your visuals.
// If you want to have a measure that
// multiplies this and the other one...

[Multiplication] = [Average] * [Measure]

// Each measure is dynamic in the sense
// that it responds to different contexts
// which you set by slicing and dicing
// on your dimensions.

Best

D

[Anonymous]

Both measures return the same result, that is good.

The bad, the result is still wrong, and for the life of me I can't figure out why. Since there is no "CALCULATE" everything should be in the same context, and yet its not adding up. See screenshot below.

First table represents the data table, you can see each unique row.
Second table shows the rows being rolled up, the math is correct on the Highlighted Column showing that it is summing correctly; 45.74 = SUM(3x rows). The measure which is calculating average is wrong in some cases, correct in others and I don't know why. Again, everything should be in context here, so its not like there is a row "hiding" somewhere that would be influcencing the calculation. If I set the AVG % column to "Average" for its summarization, I get the same incorrect result, so clearly there is something I'm missing in my data/model, I just don't know what... 😞

[Anonymous]

Minutes after typing my last response....

There is an additional level of categorization that I want to ignore. So what is the best way to roll it up to ignore that additional level of categorization and average based on the categorization level I do care about? I guess this is why I originally went down the road of a CalculatedTable, but that was still returning the same Average Result, so clearly I was doing something wrong there.

[Anonymous]

Hi

First, you have not shown a detailed example of what you start with, what you end up with and also what you WANT to end up with.

Secondly, if you want to ignore an additional level... just don't bring it into the picture. Your measures should be as general as possible and you should obtain correct results only by slicing and dicing in the right way.

By they way, where do you get those percentages from? And do you know that an average over percentages is in most circumstances not the correct way to calculate percentages in any kind of totals?

I'd suggest you post a link to a file where you have a simple model and explain in there what you get and what you want to get. Then people will be able to look at it and figure out what needs to be changed to make it work.

Cheers.

Best
D

[Anonymous]

Hello all, A family trip and a few other deadlines required me to put this to the side for a few weeks. But I'm back at it.

Please find a sample PBI file on my OneDrive.

Here is what the file looks like. The goal is that the measure currently named [Measure] (see table in lower left) would be effectively divided by the Count of BenchmarkName (illustrated in the table in the upper left). Right now it divides by the count of rows at the data's most granular level (as illustrated by the table in the lower right). The custom colums could potentially be further simplified, hence there are two right now that have the same result. 

As to averaging percentages, I did research the concerns raised, but I think in this case I'm "ok" because of the nature of the percentages and what they represent. Regardless in my mind that is a minor issue as compared to arriving at the final result I'm looking for. "Fixing" how I arrive at the % value shouldn't make a big difference compared to resolving my current impasse.

Thank you!

[harshnathani]

Hi @Anonymous ,

I have just dragged and dropped the columns from the fields to the visualization view.

The filter context of Power BI does the work for me.

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[harshnathani]

Hi @Anonymous ,

And incase you want a measure

Percent Measure =
AVERAGEX (
    FILTER (
        'Table',
        'Table'[Group2]
            = MAX ( 'Table'[Group2] )
            && 'Table'[Group3]
                = MAX ( 'Table'[Group3] )
    ),
    'Table'[Percent]
)

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , Maybe I overlooked. But I miss logic you have Average. Can you explain with example

[Anonymous]

The average is calculated as

For each unique Group2, average the Percent by each unique Group3. Hence the results shown in the second sample table where the column "AvgPCT" is the outcome of the calculations.

Hope this helps.