Nested Aggregates of multiple columns and statistical functions

[info-rchitect]

Hi,

I have been trying for days to get this measure to work with no luck.  I need to create a measure called 'SOFT_BIN_DESC_PERCENT' that is aggregated using all rows selected by a slicers for MFG_AREA_NAME_ANON, LOT_ID_ANON, SOFT_BIN_DESC_ANON, and BIN_CATEGORY_ANON.  Some background:

- The SOFT_BIN_DESC_PERCENT measure is made up of 2 parts: the sum of SOFT_BIN_DESC_COUNT for all selected rows in the table.  the sum of all QTY_IN for each unique LOT_ID_ANON for all selected rows in the table

- There is a 1:1 relationship between LOT_ID_ANON and QTY_IN.  This prevents me from suming it directly in the measure, it needs to only count each QTY_IN for each LOT_ID_ANON once.

- There are 2 weeks in the dataset, the total QTY_IN per week should be 4177 for WEEK 2022W30 and 6696 for WEEK 2022W31

Here is a snippet of the data:

WEEK	MFG_STEP_NAME	LOT_ID_ANON	QTY_IN	MFG_AREA_NAME_ANON	SOFT_BIN_DESC_ANON	SOFT_BIN_DESC_COUNT	BIN_CATEGORY_ANON
2022W31	FT1	lotid1	50	Facility1	softbin93	1	bincat25
2022W31	FT1	lotid1	50	Facility1	softbin101	1	bincat31
2022W31	FT1	lotid1	50	Facility1	softbin32	1	bincat10
2022W31	FT1	lotid1	50	Facility1	softbin1	1	bincat1
2022W31	FT1	lotid1	50	Facility1	softbin50	9	bincat16
2022W31	FT1	lotid1	50	Facility1	softbin38	37	bincat15
2022W30	FT1	lotid10	45	Facility1	softbin101	5	bincat31
2022W30	FT1	lotid10	45	Facility1	softbin65	1	bincat20
2022W30	FT1	lotid10	45	Facility1	softbin62	1	bincat20
2022W30	FT1	lotid10	45	Facility1	softbin93	3	bincat25
2022W30	FT1	lotid10	45	Facility1	softbin50	3	bincat16
2022W30	FT1	lotid10	45	Facility1	softbin38	32	bincat15
2022W31	FT1	lotid100	95	Facility2	softbin84	1	bincat21
2022W31	FT1	lotid100	95	Facility2	softbin93	2	bincat25
2022W31	FT1	lotid100	95	Facility2	softbin50	13	bincat16
2022W31	FT1	lotid100	95	Facility2	softbin38	79	bincat15
2022W30	FT1	lotid24	50	Facility1	softbin23	1	bincat5
2022W30	FT1	lotid24	50	Facility1	softbin15	1	bincat5
2022W30	FT1	lotid24	50	Facility1	softbin17	1	bincat5
2022W30	FT1	lotid24	50	Facility1	softbin50	1	bincat16
2022W30	FT1	lotid24	50	Facility1	softbin25	1	bincat28
2022W30	FT1	lotid24	50	Facility1	softbin95	0	bincat27
2022W30	FT1	lotid24	50	Facility1	softbin1	0	bincat1
2022W30	FT1	lotid24	50	Facility1	softbin22	2	bincat5
2022W30	FT1	lotid24	50	Facility1	softbin21	4	bincat5
2022W30	FT1	lotid24	50	Facility1	softbin62	5	bincat20
2022W30	FT1	lotid24	50	Facility1	softbin16	9	bincat5
2022W30	FT1	lotid24	50	Facility1	softbin20	6	bincat5
2022W30	FT1	lotid24	50	Facility1	softbin65	5	bincat20
2022W30	FT1	lotid24	50	Facility1	softbin1	3	bincat1
2022W30	FT1	lotid24	50	Facility1	softbin93	7	bincat25
2022W30	FT1	lotid24	50	Facility1	softbin38	4	bincat15

The correct summary for SOFT_BIN_DESC_PERCENT and BIN_CATEGORY_PERCENT by week for the data above is:

WEEK	MFG_STEP_NAME	SOFT_BIN_DESC_ANON	BIN_CATEGORY_ANON	WeeklyQtyIn	WeeklySoftbinDescCount	WeeklyBinCategoryCount	WeeklySoftbinDescPercent	WeeklyBinCategoryPercent
2022W30	FT1	softbin1	bincat1	95	3	3	3.16%	3.16%
2022W30	FT1	softbin15	bincat5	95	1	24	1.05%	25.26%
2022W30	FT1	softbin16	bincat5	95	9	24	9.47%	25.26%
2022W30	FT1	softbin17	bincat5	95	1	24	1.05%	25.26%
2022W30	FT1	softbin20	bincat5	95	6	24	6.32%	25.26%
2022W30	FT1	softbin21	bincat5	95	4	24	4.21%	25.26%
2022W30	FT1	softbin22	bincat5	95	2	24	2.11%	25.26%
2022W30	FT1	softbin23	bincat5	95	1	24	1.05%	25.26%
2022W30	FT1	softbin25	bincat28	95	1	1	1.05%	1.05%
2022W30	FT1	softbin38	bincat15	95	36	36	37.89%	37.89%
2022W30	FT1	softbin50	bincat16	95	4	4	4.21%	4.21%
2022W30	FT1	softbin62	bincat20	95	6	12	6.32%	12.63%
2022W30	FT1	softbin65	bincat20	95	6	12	6.32%	12.63%
2022W30	FT1	softbin93	bincat25	95	10	10	10.53%	10.53%
2022W30	FT1	softbin95	bincat27	95	0	0	0.00%	0.00%
2022W30	FT1	softbin101	bincat31	95	5	5	5.26%	5.26%
2022W31	FT1	softbin1	bincat1	145	1	1	0.69%	0.69%
2022W31	FT1	softbin32	bincat10	145	1	1	0.69%	0.69%
2022W31	FT1	softbin38	bincat15	145	116	116	80.00%	80.00%
2022W31	FT1	softbin50	bincat16	145	22	22	15.17%	15.17%
2022W31	FT1	softbin84	bincat21	145	1	1	0.69%	0.69%
2022W31	FT1	softbin93	bincat25	145	3	3	2.07%	2.07%
2022W31	FT1	softbin101	bincat31	145	1	1	0.69%	0.69%

Any help is greatly appreciated.

@tylerc  I can't attach a power-BI report which contains the raw data and the correctly summarized data, sent via email.

[info-rchitect]

Hi Jianbo Li,

I tried your suggestion but it did not work.  I will post the power-bi report that has the raw data and the correctly summarized data (I used SQL) as reference.

thx

[v-jianboli-msft]

Hi @info-rchitect ,

Based on your description, I have worked out the measure to calculate the sum of SOFT_BIN_DESC_COUNT for all selected rows in the table and the sum of all QTY_IN for each unique LOT_ID_ANON for all selected rows in the table:

_a = CALCULATE(SUM('Table'[SOFT_BIN_DESC_COUNT]),ALLSELECTED('Table'))

_b = SUMX(SUMMARIZE('Table','Table'[LOT_ID_ANON],'Table'[QTY_IN]),[QTY_IN])

Output:

Besides, how to calculate the measure SOFT_BIN_DESC_PERCENT? If it is dividing the two results?

SOFT_BIN_DESC_PERCENT =

var _a = CALCULATE(SUM('Table'[SOFT_BIN_DESC_COUNT]),ALLSELECTED('Table'))

var _b = SUMX(SUMMARIZE('Table','Table'[LOT_ID_ANON],'Table'[QTY_IN]),[QTY_IN])

return _a/_b

Final output:

If this is not what you want, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[info-rchitect]

Jianbo Li,

Here is the power-BI report with the raw data (power_bi_data) and the summarized data that I made using SQL (power_bi_data_agg_by_week).

https://www.dropbox.com/s/44e6ymz0yadvssk/bin_agg_example.pbix?dl=0

Thanks again for the help.