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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors