cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculated Table Help

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

1 ACCEPTED SOLUTION
Super User

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

2 REPLIES 2
Super User

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

Frequent Visitor

This worked perfectly. Thank you for your help!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors