Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsA,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
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
I've tried your proposal but it result a non-correct value.
Do you think it depend to the data model?
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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @fpavan76,
Could you please tell me if my solution works?
Best Regards!
Dale
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |