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

Create table or matrix to test discount sensitivity

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 numberDiscount rateAttach Flaggross margin $
1231%Y300
1244%Y400
12520%Y200
12615%N400
12711%Y300
1283%Y200
1294%N100

 

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 amtTotal 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!!

1 ACCEPTED SOLUTION
v-alq-msft
Community Support
Community Support

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:

c1.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-alq-msft
Community Support
Community Support

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

v-alq-msft
Community Support
Community Support

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:

c1.png

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

SivaMani
Resident Rockstar
Resident Rockstar

@Anonymous,

 

Can you try this?

 

SUMX(<table>,CALCULATE(SUM([Gross Margin]),FILTER(<table>,[Gross Margin] <= [Discount amt]), [Attach Flag]= "Y")) 
 
Regards,
Siva Mani
Anonymous
Not applicable

@SivaMani 

 

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.

 

Capture.PNG

 

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!!

amitchandak
Super User
Super User

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

Anonymous
Not applicable

@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.

 

Untitled.jpg

 

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!

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.