Forum Discussion
Simple IF measure
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!
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/63376and this post:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907Now 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
4 Replies
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])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.
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/63376and this post:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907Now 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
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])
