Simple IF measure

[Anonymous]

Just learning here and I searched the forum and well, was a bit overwhelmed by the multiple options for similar questions so thought I'd try my own. I can easily figure out how to add a column to do this but in reading the recommendation would be to use a measure instead since it is not a value that will be filtered.

My table looks like

Company	Sales	Commissionable	Commission
A	100	true
B	125	false
B	200	true
C	225	true

All I want to do is calculate the commissions (Sales * .10) when the Commissionable column is "true" but also that correctly calculates when aggregated at various levels, such as customer shown below. 

Company	Commissions
A
B
C

Thanks!

[amitchandak]

@Anonymous ,

new table

summarize( table, Table[Company], "Commissions", sumx( Table, if([Commissionable] ="true",.1,0)*[Sales]))

new measure

sumx(summarize( table, Table[Company], "Commissions", sumx( Table, if([Commissionable] ="true",.1,0)*[Sales])),[Commissions])

[Greg_Deckler]

@Anonymous - 

  Maybe:

Measure =
  VAR __Table = SUMMARIZE('Table',[Company],[Commissionable],"__Sum",SUM('Table'[Sales]))
  VAR __Table2 = ADDCOLUMNS(__Table,"__Commission",IF([Commissionable],[__Sum]*.1,0))
RETURN
  SUMX(__Table2,[__Commission])

[Anonymous]

Thanks Greg, is it really that complicated? It's so exceeding easy to do this as a column. I trust you know what you're doing as I'm familiar with your book, so wondering if maybe I'm not asking the question the right way. It would seem to ,e that the temp table setup would only ever allow this to work if the aggregation was on company. What if it changed to account manager? 

that commission column I'll trying to calculate is something that needs to summarized by any other possible values in the pivot. 

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

is it really that complicated? 

First, you should know that:

1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result in a visual by its row context.

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

Second, you could use this simple measure to get the result on it row context:

Measure = IF(SELECTEDVALUE('Table'[Commissionable]),SUM('Table'[Sales])*0.1)

But this will lead a measure problem, See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

and this post:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Now you will need to create another new measure as below

m_Total 1 = 
VAR __table = SUMMARIZE('Table1',[Company],[Commissionable],"__value",[m_Single])
RETURN
SUMX(__table,[__value])

Regards,

Lin