Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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.  ...
Need Help
Tutorial Requests
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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.

SivaMani's avatar
SivaMani
Icon for Resident Rockstar rankResident Rockstar
6 years ago

Anonymous,

 

Can you try this?

 

SUMX(<table>,CALCULATE(SUM([Gross Margin]),FILTER(<table>,[Gross Margin] <= [Discount amt]), [Attach Flag]= "Y")) 
 
Regards,
Siva Mani
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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.

     

     

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