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

Vote for your favorite vizzies from the Power BI Dataviz World Championship submissions. Vote now!

Reply
npowers2
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:

 

DAX Help.png

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
d_gosbell
Super User
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]))

 

View solution in original post

2 REPLIES 2
d_gosbell
Super User
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]))

 

This worked perfectly. Thank you for your help!

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

Vote for your favorite vizzies from the Power BI World Championship submissions!

Sticker Challenge 2026 Carousel

Join our Community Sticker Challenge 2026

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

January Power BI Update Carousel

Power BI Monthly Update - January 2026

Check out the January 2026 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.