Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |