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.

 

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

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.

6 Replies

  • amitchandak's avatar
    amitchandak
    Super 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!

  • SivaMani's avatar
    SivaMani
    Resident 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!!

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    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.

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    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