Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ajit_singh
Frequent Visitor

Question related to measure expression replacement using calculation items

Have a question related to measure expression replacement using calculation items as per the article:

https://www.sqlbi.com/articles/using-calculation-groups-to-selectively-replace-measures-in-dax-expre...

When [Internal Sales Amount] measure expression is changed to ":= 1", the "margin %" measure stops computing properly.

 

Steps:

  1. Downloaded the file from https://www.sqlbi.com/articles/using-calculation-groups-to-selectively-replace-measures-in-dax-expre...

  2. Changed the measure expression: Internal Sales Amount = 1

  3. Ran report "F02"

  4. "Margin %" is all 100%

Here is the changed pbix file: https://github.com/business-data-analytics/powerbi/raw/main/assets/Using%20calculation%20groups%20to...

 

Details:

 

In the definition of [Sales Amount] measure, depending on the selected "PriceToUse", the [Internal Sales Amount] measure is supposed to be "Completely" replaced by the corresponding expression stored in InternalPriceToUse[InternalPriceToUse] calculation item.

By that logic, [Internal Sales Amount] measure can have "ANY" DAX expression which would be completely replaced.

 
 

image-20210216072438587.png

 

So, when the expression of [Internal Sales Amount] is changed from

[Internal Sales Amount] := SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )

to

[Internal Sales Amount] := 1

Why "Margin %" computation stops working in report "F02"?

image-20210216072926109.png

1 ACCEPTED SOLUTION

Hi again @ajit_singh 

The issue seems to be resolved in the February 2021 Power BI Desktop update.

Not sure if it's by chance or because of raising the it on the Issues page!

 

Can you update to the latest version and try again at your end to confirm it's working now?

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

4 REPLIES 4
ajit_singh
Frequent Visitor

Sorry for the delayed reply.

 

Yes, the issue is resolved. Below is my Power BI Desktop version:

 

Version: 2.90.782.0 64-bit (February, 2021)

OwenAuger
Super User
Super User

Hi @ajit_singh 

This appears to be a bug.

 

Your reasoning is correct. Since the Calculation Items in InternalPriceToUse make no reference to SELECTEDMEASURE(), they effectively replace the selected measure with the expressions of the Calculation Items.

 

So, if a single Calculation Item is being applied, the definition of the Internal Sales Amount measure doesn't matter as it will be replaced.

 

First of all, I tried recreating the PBIX from scratch (to rule out some sort of corruption of the original PBIX), and the issue remained.

 

I then tested a few different expressions for Internal Sales Amount, some of which give the correct result.

 

So far, it seems that expressions for Internal Sales Amount returning an integer type or BLANK result in the incorrect result for Margin %.

 

 

Internal Sales Amount  Margin % correct?
SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
Yes
1.0 Yes
CONVERT( 1, DOUBLE ) Yes
DATE(2020,1,1) Yes
RAND() Yes
BLANK() No
1 No
RANDBETWEEN(1,10) No
COUNTROWS ( Sales ) No

 

I also tested a measure Margin defined as

 

 

Margin = 
[Sales Amount] - [Total Cost]

 

 

and in cases where Margin % is incorrect, Margin returns an incorrect value of approximately 4.7E+18.

This suggests the bug is creeping in when evaluating this subtraction, as both the measures Sales Amount and Total Cost individually produce correct results.

 

I suggest we report this as an issue at https://community.powerbi.com/t5/Issues/idb-p/Issues.

Suggest whoever posts the issue first posts the link back here.

 

I am going to have a look at creating a Minimal Working Example to demonstrate the issue.

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi again,

I have raised this here in the "Issues" section.

I attached a small PBIX to that post demonstrating the issue.

 

Not sure if that is the best place but I will see if I can raise it thorugh other channels. Unfortunately support tickets need Admin approval on the tenant where I have a Pro account, so will have to try that next week.

 

As far as I can tell, the issue arises when:

  • A Calculation Item returning a Double type is applied via CALCULATE to a measure returning an Integer type (or vice versa)
  • An operation is performed on the result of the CALCULATE function (such as adding/subtracting a value).

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Hi again @ajit_singh 

The issue seems to be resolved in the February 2021 Power BI Desktop update.

Not sure if it's by chance or because of raising the it on the Issues page!

 

Can you update to the latest version and try again at your end to confirm it's working now?

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors