Calcualting Revenue Only for Subset of Data

[Anonymous]

Dear Community, 

I have a sales table, where i have a Measure which gives me the amount only for Credit Memos which are for pricing. 

This is how it looks: =CALCULATE([Revenue],ARTransactionSubtypeDim[AR Transaction Sub Type]="CR MEMO",ReasonCodeDim[Reason Code]="Pricing")

When i put it in the pivot table all seems to work ok:

It automatically filters only the Regions and the Business Units which have pricing credit memos. However, i would like to add a new measure which to show me the total revenue only for this subset which has pricing credit memos... when i add my regular measure 

for revenue: =SUM(RevenueFact[Revenue Group Amount]) - that one overwrites the filtercontext and automatically adds some business units, which doesn't have pricing credit memos, but obviously have revenue:

Can you please help me get the revenue only for the rows which have pricing cedit memos?

All due respect, 

Atanas

[amitchandak]

@Anonymous , the Pricing credit memo is already the filtered revenue. to have only that revenue you need to add those filter to revenue or use visual level filters Pricing credit memo is not blank

[Anonymous]

@amitchandak  - Thank you Sir,

When i added the same filters to my revenue it gives me the exactly same amount, only for the credit memos which are pricing:

=CALCULATE(SUM(RevenueFact[Revenue Group Amount]),RevenueFact[AR Transaction Sub Type]="CR MEMO",ReasonCodeDim[Reason Code]="Pricing")

I'd like to mention in this data set i have not only credit memos, but invoices and debit memos as well.

The whole task is to get revenue (Invoices, Credit and Debit Memos) by Business unit in one measure and only the amount of pricing credit memos by business units in another. But the table has to show only the business units which have balance for pricing credit memos.

Hope this helps,

Atanas

[amitchandak]

@Anonymous , exepcted output is not clear.

one way is isinscope

if(isinscope(Table[operation unit region]),[Pricing credit memo],[revenue])

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

@amitchandak , 

I almost got it. I have one measure which computes only the pricing credit memos *-1

this is the code: =CALCULATE([Revenue],ARTransactionSubtypeDim[AR Transaction Sub Type]="CR MEMO",ReasonCodeDim[Reason Code]="Pricing")*-1

and I got the other measure to show me revenue only for the rows which have pricing credit memo amount:

=IF([Pricing Credit Memo Amount]>0,CALCULATE([Revenue],VALUES(BUDim)))

The report looks like so:

but the total for REV is not correct... it must be $260,754,400, but it still thinks i am using all the rows, not only the visible ones. 

How can i get the total only for the visible rows?

Thank you in advance, 

Atanas

[Anonymous]

@amitchandak  - I found a way to share it with google drive. 

Please see below the link to the file:

https://drive.google.com/file/d/1YyuOyLG9xAT-e9d_N2YZtqDR8A29mSmy/view?usp=sharing

Also, please let me know if access is needed and i will provide it immediately. 

In a perfect world i imagine the report as shown below on the screenshot. 

1. Measure to show only amount for credit memos with reason code "pricing" - multiplied by -1, to get positive value. 

2. Measure to show only the amount of revenue for the lines which only have amount for credit memos with reason code pricing.

3. The calculation must be done in the context of region and BU. 

Thank you for your help!

All due respect, 

Atanas

[amitchandak]

Requested for access

[Anonymous]

@amitchandak  For some reason i haven't received any notification... i sent you the sample data to your email which is pointed as contact on your facebook page.

Please let me know if you require more information,

Atanas