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
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:
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
Solved! Go to 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
You may try the ALLSELECTED Function.
http://community.powerbi.com/t5/Desktop/Percentages-to-multi-line-report/m-p/378504#M172040
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
102 | |
92 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
95 |