Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi everyone,
I ran into an error while trying to create a Calculation Group.
I have a Measure:
As you can see, the data type depends on the CalcType.
I created a Test calculation group:
With Calculation items:
1) Actual
2) Actual PY
3) Variance PY
The Matrix visual works fine with Actual and Actual PY.
However, as soon as I created the Variance PY, the visual has this error:
It relates to the CalcType=3, FORMAT([PL_%_of_Revenue_Actual],"0.00%")
I have been trying to troubleshoot the error. The possible solution is Selectedmeasureformatstring DAX measure in the Dynamic Format String.
I am unable how to apply to my Calculation Item measure.
Can someone please help?
Thanks very much
Solved! Go to Solution.
Hi @MrRong ,
To troubleshoot this problem, this function can be used in the dynamic format of the MEASURE. This DAX function returns the format string set for the currently evaluated metric. You can then use conditional logic to set the format string dynamically based on SELECTEDMEASUREFORMATSTRING.To solve this problem, this function can be used in the dynamic format of the MEASURE. This DAX function returns the format string set for the currently evaluated metric. You can then use conditional logic to set the format string dynamically based on SELECTEDMEASUREFORMATSTRING.
VAR CurrentFormatString = SELECTEDMEASUREFORMATSTRING()
VAR CalcType = SELECTEDVALUE ( PL_header[CalcType] )
RETURN
SWITCH (
TRUE(),
CalcType = 1, [PL_additive_total_Actual],
CalcType = 2, [PL_running_total_Actual],
CalcType = 3, [PL_%_of_Revenue_Actual],
CalcType = 4, [PL_%_of_running_Total_Actual]
CurrentFormatString
)
For more information on how to use , please refer to the official documentation
SELECTEDMEASUREFORMATSTRING function (DAX) - DAX | Microsoft Learn
For more information on calculating group formats, you can refer to these documents
Dynamic format strings with calculation groups - SQLBI
Controlling Format Strings in Calculation Groups - SQLBI
Best regards
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi again @MrRong
I see @Anonymous has also replied but I'll reply directly to your post.
1. Measure looks good 🙂
2. The measure's dynamic format string looks good 🙂
3. The calculation items don't look right.
Sample screenshots:
Hi @MrRong ,
To troubleshoot this problem, this function can be used in the dynamic format of the MEASURE. This DAX function returns the format string set for the currently evaluated metric. You can then use conditional logic to set the format string dynamically based on SELECTEDMEASUREFORMATSTRING.To solve this problem, this function can be used in the dynamic format of the MEASURE. This DAX function returns the format string set for the currently evaluated metric. You can then use conditional logic to set the format string dynamically based on SELECTEDMEASUREFORMATSTRING.
VAR CurrentFormatString = SELECTEDMEASUREFORMATSTRING()
VAR CalcType = SELECTEDVALUE ( PL_header[CalcType] )
RETURN
SWITCH (
TRUE(),
CalcType = 1, [PL_additive_total_Actual],
CalcType = 2, [PL_running_total_Actual],
CalcType = 3, [PL_%_of_Revenue_Actual],
CalcType = 4, [PL_%_of_running_Total_Actual]
CurrentFormatString
)
For more information on how to use , please refer to the official documentation
SELECTEDMEASUREFORMATSTRING function (DAX) - DAX | Microsoft Learn
For more information on calculating group formats, you can refer to these documents
Dynamic format strings with calculation groups - SQLBI
Controlling Format Strings in Calculation Groups - SQLBI
Best regards
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you Albert He
Hi @OwenAuger ,
Thanks for helping me out.
I followed your instructions but it is still giving me the error.
1) Measure is updated:
2) PL_Total_Amount_Actual_2 measure a dynamic format string
3) Each Calculation Item have format string
The error in the Matrix visual is:
Do my Calculation Items have correct DAX measure?
I wonder that if we need a DAX in the Dynamic format string for the 3 Calculation Items?
Thanks for your help,
Hi again @MrRong
I see @Anonymous has also replied but I'll reply directly to your post.
1. Measure looks good 🙂
2. The measure's dynamic format string looks good 🙂
3. The calculation items don't look right.
Sample screenshots:
Hi @MrRong
Yes, I believe you're correct in identifying the issue, and part of the solution (dynamic format string) 🙂
The issue is:
My recommendation would be to:
1. Adjust the PL_Total_Amount_Actual_2 measure so that it always returns a numerical value:
PL_Total_Amount_Actual_2 =
VAR CalcType =
SELECTEDVALUE ( PL_header[CalcType] )
VAR DisplayDetailCode =
SELECTEDVALUE ( PL_header[Detail] )
VAR isSubHeaderVisible =
ISFILTERED ( PL_accounts[Subheader] )
VAR Result =
SWITCH (
TRUE (),
isSubHeaderVisible = TRUE ()
&& DisplayDetailCode = 0, BLANK (),
CalcType = 1, [PL_additive_total_Actual],
CalcType = 2, [PL_running_total_Actual],
CalcType = 3, [PL_%_of_Revenue_Actual],
CalcType = 4, [PL_%_of_running_Total_Actual]
)
RETURN
Result
2. Give the PL_Total_Amount_Actual_2 measure a dynamic format string similar to this:
VAR DefaultFormat = "#,##0;(#,##0);-" -- change as needed
VAR PercentageFormat = "0.00%"
VAR CalcType =
SELECTEDVALUE ( PL_header[CalcType] )
VAR DisplayDetailCode =
SELECTEDVALUE ( PL_header[Detail] )
VAR isSubHeaderVisible =
ISFILTERED ( PL_accounts[Subheader] )
VAR Result =
SWITCH (
TRUE (),
-- This first condition can be omitted if you like, since measure is blank
isSubHeaderVisible = TRUE () && DisplayDetailCode = 0, BLANK (),
CalcType IN { 1, 2 }, DefaultFormat,
CalcType IN { 3, 4 }, PercentageFormat
)
RETURN
Result
3. For the calculation group, ensure that each calculation item has format string expression
SELECTEDMEASUREFORMATSTRING ()
Does this fix it for you?
Please post back if needed.
Regards
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
24 | |
9 | |
7 | |
6 | |
6 |
User | Count |
---|---|
29 | |
11 | |
11 | |
10 | |
6 |