A quick Measure (Division) returns me an Average as a Total in a visual table

[loic34]

Hello, 

I'm not able to give the Summarization option to the result of a quick measure (division) I created. Even though both measures divided are of Numeric and SUM type. By default I get an average (5,6) and that's it, no option whatsoever to get a SUM (I need 11,1 as the result). Both values divided are from two different tables linked as Many to Many by the field Artikel. Could you please support me here, I'm pretty stuck on something I would have hope would be easy. Many thanks

[v-yangliu-msft · Accepted solution]

Hi  @loic34 ,

Measure follows the context of the "Total" row and is calculated in that context. Therefore, using a measure in a column of a table visualization may have unexpected values in the "Total" column.

You can use the following measure, using the IF()+HASONEVALUE() function, to determine if it is Total, and if it is, you can calculate it according to your own needs, such as Average, Sum, etc.

Measure =
var _table1=
SUMMARIZE('Table','Table'[Artikel],"True_Value",[Volume divided by l.DDN1])
return
IF(
    HASONEVALUE('Table'[Artikel]),[Volume divided by l.DDN1],SUMX(_table1,[True_Value]))

Refer to:

Measure Totals, The Final Word - Microsoft Fabric Community

Dealing with Measure Totals - Microsoft Fabric Community

Data categorization in Power BI Desktop - Power BI | Microsoft Learn

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[loic34]

hello,

Thank you for the quick feedback. What would be a solution here? You see, from the visual table on the screen I provided above, I'm Dividing The Sum of Volume with the SUM of I.DDN1 (which is actually the Number of parts produced in one shift). The result is the number of shifts it takes to produce these Sum of Volume, and I need to add the shifts together to show if we have capacity. What would be the best way around it please? Thanks again

[AmiraBedh]

Can you please provide your dataset or your pbix file ?

[loic34]

I can't seem to find a way to attach pbix or excel file though

[AmiraBedh]

You can put it in a Drive folder for example and share the link/

[AmiraBedh]

You can't aggregate a measure when it is already aggregated.

here is the diff between measures and calculated columns : 

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/#:~:text=A%20measure%20is%20ev....