cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Filtering a cloumn with text

Hi

Pleas can someone help with this.  I have searched and cant find the answer to my question(s).

How to filter a column with text only without using the page or visual filters.

I am creating a report that shows how many sales have been made by source.  We have many online platforms and this leads me onto my next question.

What i was hoping to do was to filter by source.  Then each of the sources has their own fees to take into account.  Over all i am wanting to be able to create a report that does the following.

Calculates totals by source.

deduct the differnt fees per source to provide an exact profit report.

Exaple

Source 1, base fee 0.30p per transaction, 12% of total order.

Source 2, base fee 0.30p per transaction, 5% of total order.

Source 3, base fee 0.30p per transaction, 7% of total order.

Source 4, base fee 0.00p per transaction, 20% of total order.

Source 5, base fee,  15% of total order. or £1 depending what value is more

I have a calender table and date table. with most of the measures as i am 80 %complete just need this part answering.

Many thanks

Ben

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III

## Re: Filtering a cloumn with text

since you have provided very little information about your model and data, this is a bit of guess work. Are you sure you can't handle fees within a calculated column, like this:

``````sales fee =
VAR _sumFees =
transactions[total order value]
* LOOKUPVALUE (
dimFees[base transaction fee];
dimFees[source]; transactions[source]
)
+ transactions[total order value]
* LOOKUPVALUE (
dimFees[percentage of total order];
dimFees[source]; transactions[source]
)
VAR _minFee =
LOOKUPVALUE ( dimFees[minimum value]; dimFees[source]; transactions[source] )
RETURN
IF ( _sumFees > _minFee; _sumFees; _minFee )``````

if you need it as a measure you should look into the sumx-function

``````Measure =
SUMX (
VALUES ( transactions[transactionID] );
VAR _source =
SELECTEDVALUE ( transactions[source] )
VAR _minFee =
LOOKUPVALUE ( dimFees[minimum value]; dimFees[source]; _source )
VAR _baseFeePrTrans =
LOOKUPVALUE ( dimFees[base transaction fee]; dimFees[source]; _source )
VAR _totalFee =
LOOKUPVALUE ( dimFees[percentage of total order]; dimFees[source]; _source )
RETURN
IF (
CALCULATE ( SUM ( transactions[total order value] ) ) * _totalFee
+ CALCULATE ( COUNT ( transactions[transactionID] ) ) * _baseFeePrTrans < _minFee;
_minFee;
CALCULATE ( SUM ( transactions[total order value] ) ) * _totalFee
+ CALCULATE ( COUNT ( transactions[transactionID] ) ) * _baseFeePrTrans
)
)``````

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Super User III

## Re: Filtering a cloumn with text

since you have provided very little information about your model and data, this is a bit of guess work. Are you sure you can't handle fees within a calculated column, like this:

``````sales fee =
VAR _sumFees =
transactions[total order value]
* LOOKUPVALUE (
dimFees[base transaction fee];
dimFees[source]; transactions[source]
)
+ transactions[total order value]
* LOOKUPVALUE (
dimFees[percentage of total order];
dimFees[source]; transactions[source]
)
VAR _minFee =
LOOKUPVALUE ( dimFees[minimum value]; dimFees[source]; transactions[source] )
RETURN
IF ( _sumFees > _minFee; _sumFees; _minFee )``````

if you need it as a measure you should look into the sumx-function

``````Measure =
SUMX (
VALUES ( transactions[transactionID] );
VAR _source =
SELECTEDVALUE ( transactions[source] )
VAR _minFee =
LOOKUPVALUE ( dimFees[minimum value]; dimFees[source]; _source )
VAR _baseFeePrTrans =
LOOKUPVALUE ( dimFees[base transaction fee]; dimFees[source]; _source )
VAR _totalFee =
LOOKUPVALUE ( dimFees[percentage of total order]; dimFees[source]; _source )
RETURN
IF (
CALCULATE ( SUM ( transactions[total order value] ) ) * _totalFee
+ CALCULATE ( COUNT ( transactions[transactionID] ) ) * _baseFeePrTrans < _minFee;
_minFee;
CALCULATE ( SUM ( transactions[total order value] ) ) * _totalFee
+ CALCULATE ( COUNT ( transactions[transactionID] ) ) * _baseFeePrTrans
)
)``````

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!