Calculate Grand Total with Conditional

[fpavan76]

Hi to all,
I need a suggest about a measure to calculate grand total with particular condition.

I have 3 columns A, B and C
I want insert this condition:
IF A+B > C then A+B else 0

This formula work correclty in the table rows but not work in Grand Total .
May you help me to solved this issue?

Thanks in advance

Franco

[v-jiascu-msft]

Hi @fpavan76,

Could you please mark the proper answer if it's convenient for you? That will be a help to others.

Best Regards!
Dale

[MFelix]

Hi @fpavan76,

Just add this Measure to you data:

Measure=
     IF (
         SUM ( Table1[A] ) + SUM ( Table1[B] )
             > SUM ( Table1[C] ),
         SUM ( Table1[A] ) + SUM ( Table1[B] ),
        0
     )

Regards,

MFelix

[fpavan76]

Hi @MFelix

thanks for your help but I would obtain in line Total the effective sum of table rows..
Do you have any idea?

I've attach an example, in line Total of measure I would like see the sum of rows (1.041)

thanks in advance

Regards,

Franco

[MFelix]

Is your A + B + C columns a sum of several values or are they individual values in a table? you can add it as a column instead of a measure.

Regards,

MFelix

[fpavan76]

A,B and C are a measures sum of several values.

for example:
A= CALCULATE ( SUM( Table1[A1] ); FILTER ( DimDates;DimDates[Date] ) )

If I add a tabel column I don't think obtain Grand Total a correct result..

thanks in advance 

Franco

[MFelix]

Hi @fpavan76,

Try to add a column refering to the measure and use it on your table.

My measure was named Measure2 so my column will be:

ValidationColum = [Measure2]

Tell me if it works with your layout/slicers and filters.

Regards.

[fpavan76]

Hi @MFelix

i've try to add a column but is not possible because sum of measure is not permitted.
In Fact A, B and C are yet a measures.

I can try function CALCULATE but I don't know the correct usage for this issue.

May yuo have any suggestions?

Thanks in advance

Franco

[MFelix]

Hi @fpavan76,

what is the error you are having? I have made a small change in the example I have set-up and created 3 measures for A, B and C and added them to the initial measure I created, and the result is still the same in the column it doesn't give me any error.

This are simple sum measure and without complex data behind, no filter or nothing. What is the error you are getting? What type of measures are you using.

Regards,

MFelix

[fpavan76]

Hi @MFelix

I've try use your suggestion but I don't know because it work incorrectly.

In attachment you should see the Measure to calculate A,B and C (i.e. Measure A),
Measure with condition,
measure without condition.

If you observe the misure with condition the Grand Total is 28, not 30..

Thanks in advance.

Franco

[MFelix]

Hi @fpavan76,

Did you try to put the Measure with condition as a result of a column, as I said before, because when you use it in the measure the context is avalueted.

Mfelix

[fpavan76]

Hi @MFelix

I've tried your proposal but it result a non-correct value.

Do you think it depend to the data model?

[v-jiascu-msft]

Hi @fpavan76,

Please allow me to make some explanation. The picture below is from your post.

I guess the first column is "DimDate"[Date] in your visual. So, we test it if it's a row or the grand total. Because a row only has one "DimDate"[Date] while the grand total has all the "DimDate"[Date]. If the first parameter of "IF" is true, we just run [Measure A]. Or, we run [Measure A] over all the table and sum it. Please have a try.

Could you please post your formula if failed?

2Measure =
IF (
    HASONEVALUE ( 'DimDate'[Date] ) = TRUE (),
    [Measure A]
    SUMX ( 'Table', [Measure A] ),
)

Best Regards!

Dale

[MFelix]

Hi @fpavan76,

It's possible is something related with the way you have your data model, is it possible to have a sample of the data you can make a mock-up data or send it in private message.

Regards,

MFelix

[v-jiascu-msft]

Hi @fpavan76,

Could you please tell me if my solution works?

Best Regards!

Dale

[fpavan76]

Hi @v-jiascu-msft

I've tried to use your solution but return a non-correct results.
I think it's depend by the model because I've a facts table, many dimension tables and a dates table.

I don't have any idea to solve this issue ..

thanks,

Franco 

[v-jiascu-msft]

@fpavan76

Hi Franco,

We need a workaround here. In my example, we can see it works.

2Measure =
IF (
    HASONEVALUE ( 'Date'[Calendar Year] ) = FALSE (),
    SUMX ( 'Date', [Sales Amount] ),
    [Sales Amount]
)

Maybe your formula is something like this:

2Measure =
IF (
    HASONEVALUE ( 'Table1'[Column_in_table] ) = FALSE (),
    SUMX ( 'Table1', [Measure] ),
    [Measure]
)

Best Regards!

Dale