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

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

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%

1 ACCEPTED SOLUTION
Community Support

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.

Community Support

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.

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.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors