Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Still struggling with averages by a group in DAX

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

 

Group1Group2Group3Percent
1Ai5
1Bi10
1Ci15
2Ai20
2Bi25
2Ci30
3Ai40
3Bi45
3Ci50

1

Aii15
1Bii30
1Cii60
2Aii20
2Bii35
2

C

ii65
3Aii30
3Bii45
3Cii75

 

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):

Group2Group3Avg PCT
Ai21.666
Aii21.666
Bi26.666
Bii36.666
Ci31.666
Cii66.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

 

9 REPLIES 9
Anonymous
Not applicable

// 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
Not applicable

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... 😞

rpiboy1_0-1594993397879.png

 

Anonymous
Not applicable

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
Not applicable

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
Not applicable

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.

rpiboy1_0-1597264985378.png

Thank you!

harshnathani
Community Champion
Community Champion

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.

 

 

1.jpg

 

 


Regards,

Harsh Nathani


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

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

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]
)

 

 

1.jpg

 

 


Regards,

Harsh Nathani


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

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

amitchandak
Super User
Super User

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.