Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to Solution.
hi @ChicagoJay
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
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])
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 @ChicagoJay
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
88 | |
86 | |
84 | |
68 | |
49 |
User | Count |
---|---|
138 | |
111 | |
103 | |
64 | |
60 |