cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper I

## What is the suggested solution to calculate the sales percentages in the respective quarter?

For the first data of the table, it should be calculated as 5,893,082 / 26,520,122.00 = 22.22% but the measure doesn't bring the QTD (Quarter-To-Date) amount to the denominator.
What is the suggested solution to calculate the sales percentages in the respective quarter? Is there a time-intelligence formula to calculate this? Is this related to the weighted average per category percentage?

1 ACCEPTED SOLUTION
Community Support

Try this:

``````Portfolio2 % =
SUM ( Sheet1[Original Contract Amount] )
/ CALCULATE (
SUM ( Sheet1[Original Contract Amount] ),
ALLEXCEPT ( Sheet1, Sheet1[Award Date].[Year], Sheet1[Award Date].[Quarter] )
)``````

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

7 REPLIES 7
Super User

@GeorgeGokmen , You may have try like

[Orig. Contract Amt QTD]/Calculate([Orig. Contract Amt QTD],ALLEXCEPT(Date[QTR]))

Refer

https://community.powerbi.com/t5/Desktop/Percentage-of-subtotal/td-p/95390

Super User

Hi,

Assuming:

1. There is a Calendar Table which has a relationship with the Date column of your Base data; and
2. To your visual, you have dragged Year and Quarter from the Calendar Table; and
3. Orig. Contract Amt is a measure

try this measure

=[Orig. Contract Amt]/Calculate([Orig. Contract Amt],ALL(Data[Column]))

Column is the name of the column which appear under Quarter in the the matrix hierarchy of your visual.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

@Ashish_Mathur ,  thank you for your solution. Your solution worked for this calculation but now, when there is a different "column"/"category", the percentage is not being calculated by the overall sum amount in the denominator.

For example, the highlighted row has a contract value of 4,20M, and in that respective quarter (2010-Q3), the total amount is 4,51M, thus the D005 percentage should be 4.51M/4.20M = 92.96% to overall percentage. Is there a solution for these new categories? Thank you again.

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

The PBI file is in this link: this link,  and it's been cleaned up from the other measures and calculations. I couldn't replicate the sample visual from the basic excel spreadsheet in the Power BI.

Super User

Hi,

Everything appears alright to me in the bottom most table of Page 1.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Try this:

``````Portfolio2 % =
SUM ( Sheet1[Original Contract Amount] )
/ CALCULATE (
SUM ( Sheet1[Original Contract Amount] ),
ALLEXCEPT ( Sheet1, Sheet1[Award Date].[Year], Sheet1[Award Date].[Quarter] )
)``````

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