Forum Discussion

tcboutte's avatar
tcboutte
Regular Visitor
5 years ago
Solved

DAX - Multiply by Explicit Row Values

Seems like a simple thing, but am looking for some help! I need to return a value in the "GC Scenario" column equal to the value of column three (94,340.75) times the percentage ("value" - second scr...
Need Help
  • v-cazheng-msft's avatar
    v-cazheng-msft
    5 years ago

    Hi, tcboutte 

    You can try a Measure like the following.

     

    GC Scenario =

    VAR charges_sum =

        CALCULATE ( SUM ( Financial[Charges] ), DISTINCT ( Financial[Person] ) )

    VAR val =

        CALCULATE (

            ROUNDUP ( MAX(Percentage[Value]) , 2 ),

            FILTER ( Percentage, Percentage[Date] = MAX ( Percentage[Date] ) ),

            TREATAS ( VALUES ( Financial[Person] ), Percentage[Person] )

        )

    RETURN

        charges_sum * val

     

    Here is the pbix.

     

    Best Regards,

    Caiyun Zheng

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

PaulDBrown's avatar
PaulDBrown
Community Champion
5 years ago

tcboutte 

What determines which row is to be used in both tables in the calculation?

  • tcboutte's avatar
    tcboutte
    Regular Visitor
    5 years ago

    The person's name, Chen Dean, in this case. Basically, this is to calculate the value of their revenue generation by using that variable percentage rate. We also want to keep the value accurate by month so that the historical records calculate correctly, even if the percentage changes over time. 

    • PaulDBrown's avatar
      PaulDBrown
      Community Champion
      5 years ago

      tcboutte 

      How is your model set up? are the tables linked in a relationship? how are you going to show the result? a page with slicers for person's name and month? or do you only want to show the latest month's result? Is the revenue linked to a month?

      Probably best if you provide a depiction of what you actually want to show..

      • tcboutte's avatar
        tcboutte
        Regular Visitor
        5 years ago

        Thank you Paul. The tables are linked by Name and Date. Also, the visual will show a "Top N" result and the only sliver will be to select a Month. 

         

        Here's an Excel mock-up: