Cumulative return wrong over another measure (I do not have date column)

[Ito_c]

I'm trying to return cumulative percentile by fiscal week inside a fiscal quarter. But my dataset is aggregated and I dont have the transaction data.

This is my original dataset:

Fiscal Year	Fiscal Quarter	Fiscal Month	Fiscal Week	Value
FY2022	FQ02	FM04	FW16	$        27,172.31
FY2022	FQ02	FM04	FW17	$          2,491.00
FY2022	FQ02	FM05	FW19	$      174,742.22
FY2022	FQ02	FM05	FW20	$        44,876.06
FY2022	FQ02	FM05	FW21	$        78,279.50
FY2022	FQ02	FM06	FW22	$        22,681.33

I created three measures to return the Total of value, the count of fiscal week and get the percentile based on the sum of total value divided by count of fiscal week and this result divided by Total value.

Spoiler

Total Value = calculate(sum('Table'[ Value ]), allselected('Table'))

Spoiler

Fiscal Week count = calculate(distinctcount('Table'[Fiscal Week]),allselected('Table'))

Spoiler

Percentile by Fiscal Week = calculate(divide(divide([Total Value],[Fiscal Week count],0),[Total Value],0))

Now I have:

Fiscal Year	Fiscal Quarter	Fiscal Month	Fiscal Week	Value	Total Value	Fiscal Week count	Percentile by Fiscal Week
FY2022	FQ02	FM04	FW16	$        27,172.31	$  350,242.42	6	16.67%
FY2022	FQ02	FM04	FW17	$          2,491.00	$  350,242.42	6	16.67%
FY2022	FQ02	FM05	FW19	$      174,742.22	$  350,242.42	6	16.67%
FY2022	FQ02	FM05	FW20	$        44,876.06	$  350,242.42	6	16.67%
FY2022	FQ02	FM05	FW21	$        78,279.50	$  350,242.42	6	16.67%
FY2022	FQ02	FM06	FW22	$        22,681.33	$  350,242.42	6	16.67%

 So, when I try to calculate the cumulative for Percentile by Fiscal Week, the result not match as cumulative disered column. Do you know how to proper calculate the cumulative in this case?

Fiscal Year	Fiscal Quarter	Fiscal Month	Fiscal Week	Value	Total Value	Fiscal Week count	Percentile by Fiscal Week	Cumulative Desired
FY2022	FQ02	FM04	FW16	$        27,172.31	$  350,242.42	6	16.67%	16.67%
FY2022	FQ02	FM04	FW17	$          2,491.00	$  350,242.42	6	16.67%	33.34%
FY2022	FQ02	FM05	FW19	$      174,742.22	$  350,242.42	6	16.67%	50.01%
FY2022	FQ02	FM05	FW20	$        44,876.06	$  350,242.42	6	16.67%	66.68%
FY2022	FQ02	FM05	FW21	$        78,279.50	$  350,242.42	6	16.67%	83.35%
FY2022	FQ02	FM06	FW22	$        22,681.33	$  350,242.42	6	16.67%	100.00%

[v-henryk-mstf · Accepted solution]

Hi @Ito_c ,

According to your description, it is recommended to create a calculated column for cumulative statistics.

Refer to a formula similar to the following.

Sum_M = sum(FactInternetSales[SalesAmount])

cal_sum = [Sum]

cal_dy = 
VAR currentTotalSales = DimProduct[cal_sum]
RETURN
    SUMX (
        FILTER ( DimProduct, DimProduct[cal_sum] > currentTotalSales ),
        DimProduct[cal_sum]
    )

cal_account = 
DIVIDE ( DimProduct[cal_dy], SUM ( DimProduct[cal_sum] ), BLANK () )

If the problem is still not resolved, please provide detailed error information and test data. Looking forward to your reply.

Best Regards,
Henry

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