Forum Discussion

gauravnarchal's avatar
gauravnarchal
Post Prodigy
4 years ago
Solved

Issue with Measure - Row total

Hello - I have created the below measure in which row total is not showing correctly. How can i fix this issue?

 

Sale = Sumx(Values(Table[ID]) ,  CALCULATE(IF([Gross Amount]<7000,0,IF([Gross Amount]>100001,5000,([Gross Amount]*.05)))) )

 

 

 

Need Help
Show and Tell
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi gauravnarchal ,

     

    There are some errors in your code that are causing power bi to not recognize the filters you want correctly.

    Maybe you can try this code:

    Sale =
SUMX(
    'Table',
    IF(
        [Gross Amount] < 7000,
        0,
        IF( [Gross Amount] > 100001, 5000, [Gross Amount] * 0.05 )
    )
)

    result:

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    gauravnarchal , Try this

    Sale = Sumx(Summarize(Table, Table[ID], "_1" , Switch(True() ,[Gross Amount]<7000,0, [Gross Amount]>100001,5000,([Gross Amount]*.05))), [_1])

     

    Add agent and month if needed to summarize

     

    If this does not help
    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        gauravnarchal , If this does not help
        Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

         

        Sale = Sumx(Summarize(Table, Table[agent], Table[Month Year], "_1" , Switch(True() ,[Gross Amount]<7000,0, [Gross Amount]>100001,5000,([Gross Amount]*.05))), [_1])

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi gauravnarchal ,

     

    There are some errors in your code that are causing power bi to not recognize the filters you want correctly.

    Maybe you can try this code:

    Sale =
SUMX(
    'Table',
    IF(
        [Gross Amount] < 7000,
        0,
        IF( [Gross Amount] > 100001, 5000, [Gross Amount] * 0.05 )
    )
)

    result:

    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • gauravnarchal's avatar
    gauravnarchal
    Post Prodigy
    3 years ago

    v-chenwuz-msft  - I need your further help on this.

     

    I want to add another condition to this measure where

    • SUMX Table
    • if the Invoice date is equal to or less than 2 July 21 
      • If Gross Amount < 7000 return 0 or  Gross Amount > 100001 return 5000 else Gross Amount *0.05
    • if the Invoice date is equal to or greator than 1 August 21 
      • If Gross Amount < 7000, gross amount * 0.03, if Gross Amount > 100001 return 5000 else Gross Amount *0.75