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.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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!