How to calculate weekly percentage of total sum?

[balaganeshmohan]

I'd like to Calculate weekly index per item with the formula " sum of total"/"sum of a week". The data table is connected to a date table with week numbers. So each item should have a % of total amount for that item for each week.

I have tried this, but with an error "The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column." Any suggestions. Thanks for your time.

IndexLaunch = 
    VAR Total = 
    CALCULATE( SUM ( Seasonal_Index_Launch_Year[Quantity]), SELECTEDVALUE('Item'[No_] ) )

    VAR WeeklyTotal = 
    CALCULATE( SUM( Seasonal_Index_Launch_Year[Quantity]), FILTER( 'Date', 'Date'[Week Number]))

    VAR Index = 
    DIVIDE(WeeklyTotal, Total )

    RETURN Index

[balaganeshmohan]

@lbendlinThanks for the response. Sorry I do not understand what you mean by using variables. I am also attaching my sample data. I'd like to create a measure that calculates what percentage of total sum(only for that No_) that particualr No_ was sold in that week. Sorry I am quite new to PBI and experimenting with things. Would appreciate your help. Thanks!

Planned Shipment Date	Week	Quantity	No_
01/01/2017 00:00	52	1	A
03/01/2017 00:00	1	52	A
09/01/2017 00:00	2	1	A
24/11/2017 00:00	47	3	A
25/11/2017 00:00	47	130	A
27/11/2017 00:00	48	12	A
28/11/2017 00:00	48	3	A
29/11/2017 00:00	48	5	A
30/11/2017 00:00	48	1	A
01/12/2017 00:00	48	46	A
04/12/2017 00:00	49	21	A
05/12/2017 00:00	49	29	A
07/12/2017 00:00	49	0	A
08/12/2017 00:00	49	3	A
11/12/2017 00:00	50	1	A
13/12/2017 00:00	50	2	A
15/12/2017 00:00	50	1	A
18/12/2017 00:00	51	3	A
20/12/2017 00:00	51	32	A
28/12/2017 00:00	52	21	A
29/12/2017 00:00	52	1	A
01/01/2023 00:00	52	3866	B
02/01/2023 00:00	1	277	B
09/01/2023 00:00	2	131	B
12/01/2023 00:00	2	15	B
13/01/2023 00:00	2	35	B
03/04/2023 00:00	14	754	B
06/04/2023 00:00	14	28	B
10/04/2023 00:00	15	14	B
14/04/2023 00:00	15	287	B
15/04/2023 00:00	15	187	B
17/04/2023 00:00	16	304	B
25/04/2023 00:00	17	6	B
01/05/2023 00:00	18	937	B
02/05/2023 00:00	18	228	B
05/05/2023 00:00	18	379	B
15/05/2023 00:00	20	1257	B
01/06/2023 00:00	22	1098	B
05/06/2023 00:00	23	400	B
06/06/2023 00:00	23	143	B
07/06/2023 00:00	23	9	B
15/06/2023 00:00	24	61	B
26/06/2023 00:00	26	39	B
01/07/2023 00:00	26	28	B
02/07/2023 00:00	26	40	B

[lbendlin]

IndexLaunch = 
    VAR WeeklyTotal = CALCULATE( SUM('Table'[Quantity]), ALLEXCEPT('Table','Table'[Week],'Table'[No_]))
    var Total = CALCULATE( SUM('Table'[Quantity]), ALLEXCEPT('Table','Table'[No_]))
    return divide(WeeklyTotal, Total, 0 )

see attached

[balaganeshmohan]

Sorry this doesn't seem to work. I ended up everything with 100%

[lbendlin]

Please show details of your matrix visual. Same number all over usually indicates a broken data model.

[balaganeshmohan]

It works when I use ALL instead of ALLEXCEPT. Is this odd?

[lbendlin]

Show the DAX and the matrix field wells.

[lbendlin]

CALCULATE( SUM ( Seasonal_Index_Launch_Year[Quantity]), SELECTEDVALUE('Item'[No_] ) )

Don't use SELECTEDVALUE inside a CALCULATE unless it comes from a disconencted table. Use variables instead.

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...


https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523