Calculated Measure

[Anonymous]

Hi All,

I have a calculated measure which gives me values based on IF condition

Measure5 = IF(Measure1 > Measure2 && Measure3 > 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 them

For Ex:   Customer           Measure5 

               Cus1                      21% 

               Cus2                        0%

               Cus4                        0%

               Cus4                        0%

               Cus5                      32%

              Total                     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 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.

Thanks in Advance.

[itayrom]

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".

[Anonymous]

thanks for the reply @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.

"Function 'SUM' is not allowed as part of calculated column DAX expressions on DirectQuery models"

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...? 

[v-haibl-msft]

@Anonymous

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.

Best Regards,
Herbert

[Anonymous]

Hi @v-haibl-msft

I tried ur way.

and migrated to Import mode and created a 'New Column' for Measure5.

um not getting any error message but when try to use it um simply getting 0.00 value in the 'New Column'.

what did i miss.??

is there any expression to write..?

Measure5 = IF(Measure1 > Measure2 && Measure3 > Measure4, Measure1,0)

I tried both the following ways

1. New Column = (IF(Measure1 > Measure2 && Measure3 > Measure4, Measure1,0)

2. New Column = IF( Query1[Measure1] > Query1[Measure2] && Query[Measure3] > Query1[Measure4], Query1[Measure1],0 )

Consider the following image:

[jahida]

I'm jumping in late but I think there's a lot of unnecessary work being done here that could be solved with a small modification to the initial measure:

Measure5 = SUMX('Table', IF(Measure1 > Measure2 && Measure3 > Measure4, Measure1,0))

Should work in whatever mode you want.

That should give you the aggregation you're looking for (sum at the row level).

[Anonymous]

Hi, @jahida

Negative...!!

still I'm getting 0's only. Consider the following image:

[itayrom]

If Measure5 is a calculated column and Measures 1-4 are measures, I think it is awfully 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. 

Also, I believe @jahida meant for you to use SUMX() in the measure form of Measure5, not the calculated column form, if that's what you did.

[jahida]

Indeed, my formula was written as a measure, not a column. It won't just give wonky results as a column: as you saw, it will basically give nothing.

[Anonymous]

Hi,

@jahida , @itayrom and @v-haibl-msft

I think Problem is here itself,

Every calculation is created as a ‘New Measure’.

Now when I try to place Q1 CY, Q2 CY, Q3 CY and Q4 CY (which are Measure5's in this context) in a table I’m getting values as i suppose to for each and every row for individual customer.

But I'm getting total for Q1 CY only as 100 % and other totals are as 0.00%'s. what could be the issue..??

Each customer will fall under atleat one of  Q1 CY, Q2 CY, Q3 CY or Q4 CY category for sure.

Please consider the image below this is what um getting.

because of this only when i try to put this Q1 CY in a Measure as Total Q1 CY = SUMX(DimCustomer,Fact[Q1 CY])

                                                                          or in a Column as Total Q1 CY = [Q1 CY]

I'm not getting results.

Please help me out with this.

[Anonymous]

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...!!