Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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% |
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% |
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |