Revenue duplicating across line items

[Anonymous]

It's got to be a group by function in power query or something but I haven't been able to figure it out, I've tried sumx but couldn't get that to work either.

I'm trying to create a table by Sales Document # that includes Planned and Recognized Revenue by RevStream.  A single Sales Document # could include anywhere from 1-8 Rev Streams with those Streams having individual planned revenue amounts. What is happening is my Sum of Recognized Revenue is being duplicated across the individual RevStreams and reflecting the whole number for the Sales Document which is throwing off my Rev Rec %. Thanks!

Current

Sales Document	Bill To	Order Intake Date	Rev Stream	Planned Revenue	Recognized Revenue	% Recognized
874967	Customer 1	4/30/2020	Hardware	$1,886,350	$0	0%
654388	Customer 2	5/1/2020	Hardware	$269,013	$271,213	100.82%
654388	Customer 2	5/1/2020	Implementation Services	$2,200	$271,213	12327.87%

Requested

Sales Document	Bill To	Order Intake Date	Rev Stream	Planned Revenue	Recognized Revenue	% Recognized
874967	Customer 1	4/30/2020	Hardware	$1,886,350	$0	0%
654388	Customer 2	5/1/2020	Hardware	$269,013	$269,013	100%
654388	Customer 2	5/1/2020	Implementation Services	$2,200	$2,200	100%

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create two measures as below.

Recognized Revenue = 
var _flag = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        'Table'[Sales Document]=SELECTEDVALUE('Table'[Sales Document])
    )
)
return
IF(
    _flag=1,
    0,
    IF(
        _flag>1,
         SUM('Table'[Planned Revenue])
    )
)

% Recognized = 
DIVIDE(
     [Recognized Revenue],
     SELECTEDVALUE('Table'[Planned Revenue])
)

Result:

Best Regards

Allan

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

[Anonymous · Accepted solution]

I ended up creating a revenue stream abbreviation in the two tables (orders, revenue) and used combinevalues to create a link code between sales document and rev stream in both tables.

654388-IpS

654388-HW

etc...