Calculation

[Anonymous]

Hi

I have been struggling to create a table or something to use as a lookup to filter out some data for a calculation.

NOTE: Our Modeling Create New Table is not enabled as we work of a network.

I have the following scenario:

I have air travel tickets which has an "Original" and a "Refund" indicator on different rows,

thus example:  ticket 123 on row 1 is Original and amount 123

                        ticket 123 on row 2 is Refund and amount 100

I have the following calculations BUT I want to ONLY calculate/filter out the ORIGINAL AMOUNT where the Ticket Number has a REFUND indicator on the same ticket number :

(thus I need a kind of if statement or lookup that match each ticket/on ticket row level where BOTH indicators are present)

RefundAmnt = CALCULATE(SUM(Data[Spend]),Data[Travel Doc Status Desc] = "Refund")

OriginalAmnt = CALCULATE(SUM(Data[Spend]),Data[Travel Doc Status Desc]="Original")  This is where some kind of row filter needs to be added to only show the sum of tickets with both indicators

Thus the outcome must be for example out of 50 tickets overall there were 1 ticket refunded and output like :

Original Amount 123, Refund Amount 100 , Cost 23 ,Refund Tickets 1

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Please clear me with the information below.

Which is your data source?

Do you connect to your data with Power BI Desktop in live connection mode or import?

I need to know these so i can use the supported methods under your scenario.

Anyway, you could try this method first, if it doesn't help, please let me know.

create measures

original = CALCULATE(SUM('Sheet2$'[spend]),FILTER(ALLEXCEPT('Sheet2$','Sheet2$'[ticket]),[travel doc status desc]="original"))

refund = CALCULATE(SUM('Sheet2$'[spend]),FILTER(ALLEXCEPT('Sheet2$','Sheet2$'[ticket]),[travel doc status desc]="refund"))

Cost = IF([refund]<>BLANK(),[original]-[refund],BLANK())

then select items in "visual level filter" as below and "apply this filter"

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi

Our connection is via live connection from SQL Server

[v-juanli-msft]

Hi @Anonymous 

As tested, the method above should also be right for live connection.

If not, please let me know.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.