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
Ironstar
New Member

Getting the total of a measure as a constant

Hello!

 

I want the used the total of a measure as a constant for another measure, but have yet been unable to do this. Here's what I have:

image.png

I currently calculate GAP and (% ALL GRAD) - (% GRAD). However, what I want is to calculate GAP = (Total % WgtAVG; 4.39%) - % GRAD.

 

The masure for % WgtAVG =
VAR __CATEGORY_VALUES = VALUES('Equity Grads'[REPORTING_RACE_ETHNICITY])
RETURN
DIVIDE(
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE([% GRAD] * SUM('Equity Grads'[STUDENTS]))
),
SUMX(
KEEPFILTERS(__CATEGORY_VALUES),
CALCULATE(SUM('Equity Grads'[STUDENTS]))
)
)

 

 

I have tried various SUMX's on this measure to no avail and used the totals under Quick Measure as well, but not luck. 

 

Any help would be appreciated.

 

                 - - - Andrew

1 ACCEPTED SOLUTION

Sam,

 

Thanks for the idea. Unfortunately, I was unable to figure out how to apply that to get what I want, but did solve my problem another way.

 

I calculated the weigted average using:

 

% WgtAVG =
VAR __CATEGORY_VALUES = VALUES('Equity Grads'[REPORTING_RACE_ETHNICITY])
RETURN

Sumx(KEEPFILTERS(__CATEGORY_VALUES),
    Calculate(sum('Equity Grads'[STUDENTS])*SUM('Equity Grads'[GRAD])/CALCULATE(sum('Equity Grads'[STUDENTS]),ALL('Equity Grads'[GRAD_TRACKING_TERM],'Equity Grads'[REPORTING_RACE_ETHNICITY],'Equity Grads'[STUDENT_OF_COLOR]))/CALCULATE(sum('Equity Grads'[STUDENTS]),ALL('Equity Grads'[GRAD_TRACKING_TERM],'Equity Grads'[REPORTING_RACE_ETHNICITY],'Equity Grads'[STUDENT_OF_COLOR]))))

 

Then, I summed the % WgtAVG using:

 

% Total WgtAVG = sumx(all('Equity Grads'[REPORTING_RACE_ETHNICITY],'Equity Grads'[STUDENT_OF_COLOR]),'Equity Grads'[% WgtAVG])

 

I am still working through the logic of why these work, but they work under all the circumstances I need them.

 

                 - - - Andrew

View solution in original post

2 REPLIES 2
v-chuncz-msft
Community Support
Community Support

@Ironstar,

 

You may try the ALLSELECTED Function.

http://community.powerbi.com/t5/Desktop/Percentages-to-multi-line-report/m-p/378504#M172040

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Sam,

 

Thanks for the idea. Unfortunately, I was unable to figure out how to apply that to get what I want, but did solve my problem another way.

 

I calculated the weigted average using:

 

% WgtAVG =
VAR __CATEGORY_VALUES = VALUES('Equity Grads'[REPORTING_RACE_ETHNICITY])
RETURN

Sumx(KEEPFILTERS(__CATEGORY_VALUES),
    Calculate(sum('Equity Grads'[STUDENTS])*SUM('Equity Grads'[GRAD])/CALCULATE(sum('Equity Grads'[STUDENTS]),ALL('Equity Grads'[GRAD_TRACKING_TERM],'Equity Grads'[REPORTING_RACE_ETHNICITY],'Equity Grads'[STUDENT_OF_COLOR]))/CALCULATE(sum('Equity Grads'[STUDENTS]),ALL('Equity Grads'[GRAD_TRACKING_TERM],'Equity Grads'[REPORTING_RACE_ETHNICITY],'Equity Grads'[STUDENT_OF_COLOR]))))

 

Then, I summed the % WgtAVG using:

 

% Total WgtAVG = sumx(all('Equity Grads'[REPORTING_RACE_ETHNICITY],'Equity Grads'[STUDENT_OF_COLOR]),'Equity Grads'[% WgtAVG])

 

I am still working through the logic of why these work, but they work under all the circumstances I need them.

 

                 - - - Andrew

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.