SUMIF as DAX measure

[serky]

Hi

can someone advise how to execute a calculation over a subset of rows only?

In my case, I have a table that lists customers and their individual sales. I need a dynamic measure that loops over each customer, and adds their sales to give a total figure.

Example -

Customer, Sale, Total Sale
Jane, 1, 10
Fred, 2, 3
Tom, 1, 1
Jane, 9, 10
Fred, 1,3

I need a measure not a calculated column.

Thanks

 

[serky]

Ok - I have finally found an answer to this. Posted it below to help others.

Total sales:= CALCULATE(SUM(Table[Sale]),
ALLEXCEPT(Table, Table[Customer]))

Kudos to Zubair_Muhammad and their post: https://community.powerbi.com/t5/De...another-column-based-on-row-value/td-p/374445

There is also a reference to this function: https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

though more than happy for someone to add their explanation re how this works.

 

[serky]

I would like to extend this questions further. I now need another SUMIF to consider both a column in the table and the result of a measure eg - I want to know the total sales for each person for a given supervisor (the supervisor can change with different shifts). The supervisor is determined by a measure.

Example -

<colgroup><col width="65" span="4"> </colgroup><tbody>
[TD="class: xl27, width: 65"]Customer[/TD]
[TD="class: xl27, width: 65"]Sale[/TD]
[TD="class: xl27, width: 65"]Supervisor[/TD]
[TD="class: xl27, width: 65"]Total Sale[/TD]

[TD="class: xl26"]Jane[/TD]
[TD="class: xl26"]1[/TD]
[TD="class: xl26"]Jack[/TD]
[TD="class: xl26"]1[/TD]

[TD="class: xl24"]Fred[/TD]
[TD="class: xl24"]2[/TD]
[TD="class: xl24"]Jack[/TD]
[TD="class: xl24"]3[/TD]

[TD="class: xl24"]Tom[/TD]
[TD="class: xl24"]1[/TD]
[TD="class: xl24"]Jack[/TD]
[TD="class: xl24"]1[/TD]

[TD="class: xl24"]Jane[/TD]
[TD="class: xl24"]9[/TD]
[TD="class: xl24"]Henry[/TD]
[TD="class: xl24"]11[/TD]

[TD="class: xl24"]Fred[/TD]
[TD="class: xl24"]1[/TD]
[TD="class: xl24"]Jack[/TD]
[TD="class: xl24"]3[/TD]

[TD="class: xl25"]Jane[/TD]
[TD="class: xl25"]2[/TD]
[TD="class: xl25"]Henry[/TD]
[TD="class: xl25"]11[/TD]

</tbody>

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} .font5 {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl24 {text-align:center; border-top:.5pt hairline windowtext; border-right:.5pt solid windowtext; border-bottom:.5pt hairline windowtext; border-left:.5pt solid windowtext;} .xl25 {text-align:center; border-top:.5pt hairline windowtext; border-right:.5pt solid windowtext; border-bottom:.5pt solid windowtext; border-left:.5pt solid windowtext;} .xl26 {text-align:center; border-top:none; border-right:.5pt solid windowtext; border-bottom:.5pt hairline windowtext; border-left:.5pt solid windowtext;} .xl27 {font-weight:700; text-align:center; border:.5pt solid windowtext;} ruby {ruby-align:left;} rt {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0; mso-char-type:none; display:none;} --> </style>
I have tried a couple of variations to the formula above but it's not quite working.

Thanks