Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Have a question related to measure expression replacement using calculation items as per the article:
When [Internal Sales Amount] measure expression is changed to ":= 1", the "margin %" measure stops computing properly.
Steps:
Downloaded the file from https://www.sqlbi.com/articles/using-calculation-groups-to-selectively-replace-measures-in-dax-expre...
Changed the measure expression: Internal Sales Amount = 1
Ran report "F02"
"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.
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"?
Solved! Go to 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
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)
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
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:
Regards,
Owen
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
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |