Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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
sturlaws
Resident Rockstar
Resident Rockstar

@Anonymous ,

 

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.

View solution in original post

1 REPLY 1
sturlaws
Resident Rockstar
Resident Rockstar

@Anonymous ,

 

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.