How to calculate sum of rows and overall percentage of sum?

[DRasmussen]

I have the following table where I am trying to calculate the overall compliance (sum of numerator for VBP and NVBP/sum of denominator for VBP and NVBP) for each measure for each group. My table looks like the following:

CustomerMemberGroupName	VBP_Type	Measure Description	Numerator	Denominator	Compliance Rate
Group 1	NVBP	BCS-Breast Cancer Screening	28	42	67%
Group 1	NVBP	CCS-Cervical Cancer Screening	141	185	76%
Group 1	NVBP	COL-Colorectal Cancer Screening	65	126	52%
Group 1	NVBP	W34-Well-Child Visits in the Third, Fourth, Fifth and Sixth Years of Life	45	69	65%
Group 1	VBP	BCS-Breast Cancer Screening	26	28	93%
Group 1	VBP	CCS-Cervical Cancer Screening	117	132	89%
Group 1	VBP	COL-Colorectal Cancer Screening	40	51	78%
Group 1	VBP	W34-Well-Child Visits in the Third, Fourth, Fifth and Sixth Years of Life	71	90	79%
Group 2	NVBP	BCS-Breast Cancer Screening	51	78	65%
Group 2	NVBP	CCS-Cervical Cancer Screening	87	143	61%
Group 2	NVBP	COL-Colorectal Cancer Screening	83	195	43%
Group 2	NVBP	W34-Well-Child Visits in the Third, Fourth, Fifth and Sixth Years of Life	10	23	43%
Group 2	VBP	BCS-Breast Cancer Screening	15	17	88%
Group 2	VBP	CCS-Cervical Cancer Screening	18	22	82%
Group 2	VBP	COL-Colorectal Cancer Screening	28	38	74%
Group 2	VBP	W34-Well-Child Visits in the Third, Fourth, Fifth and Sixth Years of Life	1	1	100%
Group 3	NVBP	BCS-Breast Cancer Screening	108	151	72%
Group 3	NVBP	CCS-Cervical Cancer Screening	216	295	73%
Group 3	NVBP	COL-Colorectal Cancer Screening	171	266	64%
Group 3	NVBP	W34-Well-Child Visits in the Third, Fourth, Fifth and Sixth Years of Life	38	48	79%
Group 3	VBP	BCS-Breast Cancer Screening	48	55	87%
Group 3	VBP	CCS-Cervical Cancer Screening	94	118	80%
Group 3	VBP	COL-Colorectal Cancer Screening	86	106	81%
Group 3	VBP	W34-Well-Child Visits in the Third, Fourth, Fifth and Sixth Years of Life	32	44	73%

Would I create a measure or several measures? I am unsure how to perform this calculation for my report. Can someone help?
Thanks so much,

Dea

[parry2k]

@DRasmussen is this how your raw data looks like or your numerator and denominator are measure? In the exmaple you gave, for each row seems like you are diving N with D, correct?

[DRasmussen]

Hi Parry,

This is the raw data. The numerators and denominators are not calculated fields or measures.

[parry2k]

@DRasmussen seems like this is pretty straight forward until I'm missing something, add following measure

Compliace Rate = 
DIVIDE ( SUM ( Table[Numerator] ), SUM ( Table[Denominator ) )

[DRasmussen]

It seems like we're almost there, except it is providing the compliance rate for all the groups aggregated together instead of by measure for each group. Does something else need to be added to the measure?

[v-lili6-msft · Accepted solution]

hi @DRasmussen 

This is a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

For your case, just add a new measure as below:

Measure =

VAR _table =

    SUMMARIZE (

'Table',

        'Table'[CustomerMemberGroupName],

        'Table'[VBP_Type],

        'Table'[Measure Description],

        "_value", [Compliace Rate]

    )

RETURN

    SUMX ( _table, [_value] )

Regards,

Lin