Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
60 | |
55 |
User | Count |
---|---|
179 | |
108 | |
105 | |
71 | |
70 |