Forum Discussion

alicewang96's avatar
alicewang96
Helper I
5 years ago

Using DIVIDE, SUM, and EARLIER

I'm trying to create a measure using DIVIDE, SUM, and MAX. The DAX expression I'm trying to use is :

New Measure = DIVIDE(
    CALCULATE(
        SUM(table_1[Net Amount Paid]), table_2[ID] = "12345"), 10000000),
        'date_table'[Dispense Date] <= MAX('date_table'[Dispense Date])
)
 
What this should be doing is summing together the Net Amount Paid from table_1 WHEN the ID in table_2 is 12345. Then this divides the sum by 10M. Since the visual will be displayed by year, I am using MAX(date_table) to show a running total for each year so:
 
Year    Spend  % Spent
2018    100K    1% (100K/10M)    
2019    500K     6% ((100K+500K)/10M))
Need Help

12 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    alicewang96 , Try like

    DIVIDE(
    CALCULATE(
    SUM(table_1[Net Amount Paid]), table_2[ID] = "12345", filter(date_table,'date_table'[Dispense Date] <= MAX('date_table'[Dispense Date]))), 10000000)

    • alicewang96's avatar
      alicewang96
      Helper I
      5 years ago

      I'm getting the error:

      The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        alicewang96 , Try this

        DIVIDE(
        CALCULATE(
        SUM(table_1[Net Amount Paid]), filter(table_2, table_2[ID] = "12345"), filter(date_table,'date_table'[Dispense Date] <= MAX('date_table'[Dispense Date]))), 10000000)

         

        hope Net Amount Paid is a column not measure. If measure do not use sum

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    alicewang96 - Not sure about the EARLIER part, but maybe:

    New Measure = 
  DIVIDE(
    SUMX(FILTER(table_1), table_2[ID] = "12345" && 'date_table'[Dispense Date] <= MAX('date_table'[Dispense Date])),[Net Amount Paid]),
    10000000
  )
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Create a Calendar Table and create a calculated column for Year.  Build a relationship from the Date column of your table_1 to the  Date column of the Calendar Table.  To your visual, drag Year from the Calendar Table.  Write this measure

    New Measure = DIVIDE(CALCULATE(SUM(table_1[Net Amount Paid]), table_2[ID] = "12345"),datesbetween(Calendar[date],MINX(ALL(Calendar),Calendar[date]),max(calendar[date]))),10000000))
    Hope this helps.  If it does, then share the link from where i can download your PBI file.
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi alicewang96 ,

    I am not clear about the relationship between date_table and table_1 in the information you provided.

    In my understanding, you want to calculate the total from that year to the present,right?

    You could use the following fomula:

     

    yearColumn =
YEAR ( 'Table_1'[Date] )
    Measure =
DIVIDE (
    CALCULATE (
        SUM ( 'Table_1'[Net Amount Paid] ),
        FILTER (
            ALL ( Table_1 ),
            'Table_1'[ID] = 12345
                && 'Table_1'[yearColumn] <= MAX ( [yearColumn] )
        )
    ),
    10000000
)

     

    My visualization looks like this:

    Did I answer your question ? Please mark my reply as solution. Thank you very much.

    If not, please upload some insensitive data samples and expected output.

     

    Best Regards,

    Eyelyn Qin

     

    • alicewang96's avatar
      alicewang96
      Helper I
      5 years ago

      Anonymous  The [ID] column isn't in table_1, it's in a separate table called table_2. How can I adjust your expression to account for this? I've tried adding an extra FILTER after && for table_2, but it's giving me an error.