Calculated Measure

Question

Hi All,&nbsp;I have a calculated measure which gives me values based on IF condition&nbsp;Measure5 = IF(Measure1 &gt; Measure2 &amp;&amp; Measure3 &gt; Measure4, Measure1,0)When i use this measure for a dimension say 'Customer' in a table viz, i am getting exact values as:Measure1 values for some customers and 0's for rest of themFor Ex: &nbsp; Customer &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Measure5&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Cus1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;21%&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Cus2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Cus4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Cus4 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Cus5 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;32%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Total &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100%Actually Measure5 will give Percentage values thats y um getting 100% in the 'Total' row.But my requirement is to get the total of the Measure5 values that is (21+32 = 53%) in the total row instead of getting 100% in the total row.How can i achieve this..?I have seen that the 'Default Summarization' Property in the 'Modeling' tab is greyed&nbsp;out for every 'Measure', um not even sure that this will gonna give me the exact solution what um looking for, if i change the default summarization property to sum or something.guy's please help me out with this.&nbsp;Thanks in Advance.

itayrom · Answer

The "Default Summarization" property is greyed out because measures are inherently summarizations, since they basically perform calculations on groups of values and return a single result. Otherwise, they would just be calculated columns.

The totals shown in visuals that request sub-totals from Power BI(such as the Table and Matrix visuals) are the result of the summarization function performed on the values of all the items for whom the sub-totals were requested. Therefore, the 100% total you're getting is not the summation of each customer's Measure5 result, but rather the result of Measure5 calculated on all the customers. The same goes for all the measures used internally by Measure5(I'd recommend reading about row and filter contexts in DAX if you're not already familiar with that topic).

If you'd like the total to be a summation, one option would be implementing Measure5 as a calculated column(if at all possible) and set its summarization option to "Sum".

itayrom · Answer

If Measure5 is a calculated column and Measures 1-4 are measures, I think it is awfully&nbsp;wrong to use them in Measure5's expression. The reason is that calculated columns are evaluated on creation and measures are evaluated on the fly, which means that whatever you get in Measure5 reflects the results of its expression at the moment of creation. I.e. the results will not be adjusted according to changes that affect Measures 1-4, which may lead to some wonky results.&nbsp;&nbsp;Also, I believe&nbsp;jahida&nbsp;meant for you to use SUMX() in the measure form of Measure5, not the calculated column form, if that's what you did.

anonymous · Answer

thanks for the reply&nbsp;itayrom.all the columns um using in the calculation are measures and when i try to create a calulated column instead of a measure um getting the following error.&nbsp;"Function 'SUM' is not allowed as part of calculated column DAX expressions on DirectQuery models"&nbsp;Should i create calculated columns instead of measures for all those Measure1 to Measure4 to perform the sum operation for Measure5 which should also be a calculated column instead of a measure...?&nbsp;

v-haibl-msft · Answer

Anonymous&nbsp;I guess that you’ve used SUM function in Measure1 to Measure4. According to this article, SUM is only supported in measure when using DirectQuery mode.If you use Import mode here, you should be able to get the expected result by only changing Measure5 to Column5.&nbsp;&nbsp;Best Regards,Herbert

anonymous · Answer

yes exactly I've used Calculate and SUM functions in all measures.Yeah um trying to go for import mode.thanks for the reply.I ll get back to you if i have any...!! &nbsp;

Forum Discussion

Calculated Measure

10 Replies

Related content

DAX Calculated Measure

DAX Measure for Ranking the valid rows and calculating the average rating

Measure or Calculated Column

Measure to calculate

Measure/Model calculation support needed