Revenue proportion ignoring negatives with correct totals

[sviv321]

Hi,

I am new to Power BI.

I am trying to calculate proportion of revenue by clients & by products (Output Table below).

I am able to calcultae the "Revenue %" column; but i am not successful in getting the last two columns "Revenue (excl -ve clients)" "Revenue % (Only considering Net +ve Clients)".

In this column I want to ignore clients with negative revenue (after summing up client level revenue). 

I have a slicer on report page to select the Product.

Also, I will use this % output to calculate further measures.

Any help would be great.

Below are the required tables:

Data Table Input
Product	Client	Revenue
A	1	10
A	1	-20
A	2	30
A	2	40
A	3	20
A	4	45
A	4	25
B	2	-40
B	1	10
B	1	20
B	3	20
B	3	10
B	4	45
B	4	25

Output Required
Product	Client	Revenue	Revenue %	Revenue (excl -ve clients)	Revenue % (Only considering Net +ve Clients)
A	1	-10	-6.7%	-	0.0%
A	2	70	46.7%	70	43.8%
A	3	20	13.3%	20	12.5%
A	4	70	46.7%	70	43.8%
Total		150	100.0%	160	100.0%
Product	Client	Revenue	Revenue %	Revenue (excl -ve clients)	Revenue % (Only considering Net +ve Clients)
B	1	30	33.3%	30	23.1%
B	2	-40	-44.4%	-	0.0%
B	3	30	33.3%	30	23.1%
B	4	70	77.8%	70	53.8%
Total		90	100.0%	130	100.0%

[v-rzhou-msft · Accepted solution]

Hi @sviv321 

I have a test by your sample, I think you need to add a PC code column to build Revenue % (Only considering Net +ve Clients).

PC Code = 'Data Table Input'[Product]&""&'Data Table Input'[Client]

Then let's build measures to show the result.

Revenue% = 
DIVIDE (
    SUMX (
        SUMMARIZE (
            'Data Table Input',
            'Data Table Input'[Product],
            'Data Table Input'[Client],
            'Data Table Input'[Revenue]
        ),
        [Revenue]
    ),
    SUMX ( ALLSELECTED ( 'Data Table Input' ), 'Data Table Input'[Revenue] )
)

%Revenue (excl -ve clients) = 
VAR _T =
    SUMMARIZE (
        'Data Table Input',
        'Data Table Input'[Product],
        'Data Table Input'[Client],
        "Revenue", SUM ( 'Data Table Input'[Revenue] )
    )
RETURN
    SUMX ( FILTER ( _T, [Revenue] > 0 ), [Revenue] )

Revenue % (Only considering Net +ve Clients) = 
VAR _PCcode =
    CALCULATETABLE (
        VALUES ( 'Data Table Input'[PC Code] ),
        FILTER ( ALL ( 'Data Table Input' ), 'Data Table Input'[Revenue] < 0 )
    )
VAR _Total =
    SUMX (
        FILTER (
            ALLSELECTED ( 'Data Table Input' ),
            NOT ( 'Data Table Input'[PC Code] IN _PCcode )
        ),
        'Data Table Input'[Revenue]
    )
RETURN
    DIVIDE ( [%Revenue (excl -ve clients)], _Total ) + 0

Result is as below.

Select A:

Select B:

Best Regards,

Rico Zhou

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

[sviv321]

@v-rzhou-msft 

It worked for me. Thanks for your help!!! 🙂 

[amitchandak]

@sviv321 , Try a measure like

calculate(divide(sum(Table[Revenue]), calculate(sum(Table[Revenue]), allselected(Table))), filter(Table, Table[Revenue]>0))

[sviv321]

@amitchandak ,

Thanks for the quick response.

I tried the formula you suggested, but it is returning 100% for all rows.

I even tried replacing SUM with SUMX, but that didn't help either.