Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am working on a metric board and need to summarize my data set in a calculated table. I was able to generate the table, but am now having an issue with which row my formula is pulling data from:
I need the % formula to use the ROLLUP associated with each week (yellow), not from the grand total (blue). Any help would be greatly appreciated.
Summary Table =
var _Tested = COUNTX(Query1,Query1[SampleNumber])
return
SUMMARIZE(
Query1,
ROLLUP(Query1[Week],Query1[Profile Result]),
"Row Count",
COUNTA(Query1[SampleNumber]),
"%",if(
NOT(ISSUBTOTAL(Query1[Profile Result])||ISSUBTOTAL(Query1[Week]))
,DIVIDE(COUNTA(Query1[SampleNumber]),_Tested)
),
"Week Subtotal",ISSUBTOTAL(Query1[Week]),
"Profile Subtotal",ISSUBTOTAL(Query1[Profile Result]),
"Total",ISSUBTOTAL(Query1[Profile Result])&&ISSUBTOTAL(Query1[Week]))
Solved! Go to Solution.
So the problem is that you are calculating the "blue" value and storing that in a variable and using that for the denominator in the call to DIVIDE. I think the following alteration replacing the variable with a CALCULATE expression might work.
Summary Table =
SUMMARIZE(
Query1,
ROLLUP(Query1[Week],Query1[Profile Result]),
"Row Count",
COUNTA(Query1[SampleNumber]),
"%",if(
NOT(ISSUBTOTAL(Query1[Profile Result])||ISSUBTOTAL(Query1[Week]))
,DIVIDE(COUNTA(Query1[SampleNumber]),
/* The following should calculate the total count for the week */
CALCULATE(COUNTX(Query1,Query1[SampleNumber]),ALL(Query1[Profile Result]) ) )
),
"Week Subtotal",ISSUBTOTAL(Query1[Week]),
"Profile Subtotal",ISSUBTOTAL(Query1[Profile Result]),
"Total",ISSUBTOTAL(Query1[Profile Result])&&ISSUBTOTAL(Query1[Week]))
So the problem is that you are calculating the "blue" value and storing that in a variable and using that for the denominator in the call to DIVIDE. I think the following alteration replacing the variable with a CALCULATE expression might work.
Summary Table =
SUMMARIZE(
Query1,
ROLLUP(Query1[Week],Query1[Profile Result]),
"Row Count",
COUNTA(Query1[SampleNumber]),
"%",if(
NOT(ISSUBTOTAL(Query1[Profile Result])||ISSUBTOTAL(Query1[Week]))
,DIVIDE(COUNTA(Query1[SampleNumber]),
/* The following should calculate the total count for the week */
CALCULATE(COUNTX(Query1,Query1[SampleNumber]),ALL(Query1[Profile Result]) ) )
),
"Week Subtotal",ISSUBTOTAL(Query1[Week]),
"Profile Subtotal",ISSUBTOTAL(Query1[Profile Result]),
"Total",ISSUBTOTAL(Query1[Profile Result])&&ISSUBTOTAL(Query1[Week]))
This worked perfectly. Thank you for your help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |