Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Everyone,
I am looking to set up a table that sums up profit dollars against transaction discount, given discount values i set, to see at what point the discount rate becomes unsustainable.
I have the following data:
Transaction number | Discount rate | Attach Flag | gross margin $ |
123 | 1% | Y | 300 |
124 | 4% | Y | 400 |
125 | 20% | Y | 200 |
126 | 15% | N | 400 |
127 | 11% | Y | 300 |
128 | 3% | Y | 200 |
129 | 4% | N | 100 |
and i would like to create a table or matrix (whatever works) like the following, where the total gross margin sum is everything less than or equal to the discount amount i have specified, that also has the attach flag "Y":
Discount amt | Total gross margin sum |
1% | 300 |
2% | 300 |
4% | 900 |
6% | 900 |
8% | 900 |
10% | 900 |
12% | 1200 |
14% | 1200 |
16% | 1200 |
If this were excel i would just use the following idea, IE: sum up the gross margin amount less than or equal to the discount amount, that has the attach flag, but i just cant seem to wrap my head around how to setup this table in Power BI.
SUMIFS([gross margin $],[Discount Rate],"<="&[Discount amt],[Attach Flag],"Y")
Any help is appreciated!
Thank you!!
Solved! Go to Solution.
Hi, @Anonymous
Based on your description, you may create a measure as follows.
SumMargin =
var _currentamt = SUM(DiscAmt[Discount amt])
return
SUMX(
FILTER(
ALL(TransactionData),
TransactionData[Discount rate]<=_currentamt&&
TransactionData[Attach Flag] = "Y"
),
CALCULATE(SUM(TransactionData[gross margin $]))
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.
Best Regards
Allan
Hi, @Anonymous
Based on your description, you may create a measure as follows.
SumMargin =
var _currentamt = SUM(DiscAmt[Discount amt])
return
SUMX(
FILTER(
ALL(TransactionData),
TransactionData[Discount rate]<=_currentamt&&
TransactionData[Attach Flag] = "Y"
),
CALCULATE(SUM(TransactionData[gross margin $]))
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous,
Can you try this?
Hi, and thanks for your reply!
I gave what you recommend a try and constructed the following measure:
Measure = SUMX(TransactionData,
CALCULATE(SUM(TransactionData[gross margin $]),
FILTER(DiscAmt,DiscAmt[Discount amt]>=TransactionData[Discount rate])
,TransactionData[Attach Flag]= "Y"))
However, i dont quite get the intended result, it looks like it just sums up the total for every line that has the attach flag.
I demoed it out in a powerbi file you can grab from here, to see what i have done. Download it here.
If you could recommend anything else, that would be very much appreciated!
Thanks so much!!
Did not get conditions. But in power bi
calculate(sum(table[gross Margin$]),[Discount Rate]"<="[Discount amt],[Attach Flag],"Y")
Or
sumx(filter(table,[Discount Rate]"<="[Discount amt] &&[Attach Flag],"Y"),[gross Margin$])
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
@amitchandak Thanks for your reply!
I am having trouble using those formulas, particularly in the [Discount Rate]<=[Discount amt] portion of the formula. My "Discount amt" comes from a different table than the "Discount Rate". I use a table called DiscAmt to specify the discount rates i am looking to measure, it seems like i cant related the two tables in a calculate or a sumx like this.
Ive attached the file so you can take a look: Download it here.
If you have any other suggestions i would very much appreciate it.
Thanks!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |