Divide Latest week value with sum of Sales

[Junaid11]

Hello,

I want to divide the latest AR value with sum of Salesand then multiply it with number of Days. But when I am dividing it with total sales value it is automatically dividing it with all week sales. I am attaching my data as well as outcome I want to have. Kindly have a look at it. A help would be highly appreciated.

Thank you

Weeknum	Sales	AR	Days in week
1	$    594,935.76	$      343,466,767	7
2	$    423,051.96	$        67,955,894	14
3	$    378,467.79	$              568,569	21
4	$    379,376.90	$           8,990,955	28
5	$    459,192.74	$        17,413,341	35
6	$ 1,065,006.10	$        25,835,727	42
7	$    642,491.90	$           5,201,533	49

Outcome I need is for week 7 it would be ($ 5,201,533/$ 3,942,523.15) * 49 = 64.533

64.533 would be the outcome for week 7.

[v-yiruan-msft · Accepted solution]

Hi @Junaid11 ,

You can create a measure as below to get it, please find the details in the attachment.

Measure = 
VAR _selweek =
    SELECTEDVALUE ( 'Table'[Weeknum] )
VAR _sales =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Weeknum] <= _selweek )
    )
VAR _ar =
    CALCULATE (
        SUM ( 'Table'[AR] ),
        FILTER ( 'Table', 'Table'[Weeknum] = _selweek )
    )
VAR _days =
    CALCULATE (
        MAX ( 'Table'[Days in week] ),
        FILTER ( 'Table', 'Table'[Weeknum] = _selweek )
    )
RETURN
    DIVIDE ( _ar, _sales ) * _days

Best Regards

[PijushRoy]

Hi @Junaid11 

Please try this

Measure = DIVIDE(SUM('Table'[ AR ]),CALCULATE(SUM('Table'[ Sales ]),ALL('Table '[Weeknum])))*SELECTEDVALUE('Table'[Days in week])

If solve your requirement, please mark this answer as SOLUTION
If this comment helps you, please LIKE this comment/Kudos