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
Anonymous
Not applicable

Restrict format string to a particular Calculation item in calculation groups

Hello All,

In our tabular model, we have created a calculation group as Period Compare,

and it has the items as Cur, PP1, Diff, Diff%.

 

 

 

Cur:- Selectedmeasure()

PP1:- 
CALCULATE ( 
        SELECTEDMEASURE(),
        DATEADD('DATEMASTER'[DATE], -1, YEAR)
    )
Diff:- 
VAR sel_measure = IF (ISBLANK(SELECTEDMEASURE()), 0, SELECTEDMEASURE())
VAR prev_measure = IF (ISBLANK(CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR ( 'DATEMASTER'[DATE] ))), 0, CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR ( 'DATEMASTER'[DATE] )))
RETURN	
sel_measure - prev_measure

Diff%:-
VAR prev_measure = IF (ISBLANK(CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR ( 'DATEMASTER'[DATE] ))), 0, CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR ( 'DATEMASTER'[DATE] )))
VAR sel_measure = IF( ISBLANK(SELECTEDMEASURE ()), 0, SELECTEDMEASURE ())

RETURN
    IF(
        ISBLANK (prev_measure) && ISBLANK(sel_measure), 
        BLANK(),
             DIVIDE (sel_measure - prev_measure, prev_measure)
       )

 

 

Now to show the calculation items in their respected selected measure format, we have used FORMAT STRING option and it worked.

But after Microsoft support guys suggestion, we have removed that beause its causing huge performance issue keeping model size and the amount of data we are dealing with in mind.

 

Now the major issue here in these calculation items is, 

for Diff% if we dont mention that FORMAT STRING as "%" then it would jus give whatever the measure we selected, that measure's  format.

Like if i select Revenue as a measure which is Currency format, then even Diff% also shows the Currency%. 

 

We tried to use FORMAT() function to convert it, but it also degrades the performance and it converts the values into STRING which can not be used in any another visual than except matrix and table visual.

 

So keeping all these limitations in consideration, client is agreed to show the DIFF% values in one decimal without % symobol 

like ex: 98.23

 

We been struggling to get this thing done.

 

Any suggestions or help if i get, that would be much appreciated.

 

Thanks,

Mohan V.

 

 

2 REPLIES 2
v-kkf-msft
Community Support
Community Support

Hi @Anonymous ,

 

Try the following formula:

 

Diff%:-
VAR prev_measure = IF (ISBLANK(CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR ( 'DATEMASTER'[DATE] ))), 0, CALCULATE(SELECTEDMEASURE(), SAMEPERIODLASTYEAR ( 'DATEMASTER'[DATE] )))
VAR sel_measure = IF( ISBLANK(SELECTEDMEASURE ()), 0, SELECTEDMEASURE ())

RETURN
    IF(
        ISBLANK (prev_measure) && ISBLANK(sel_measure), 
        BLANK(),
             DIVIDE (sel_measure - prev_measure, prev_measure) * 100 & "%"
       )

 

Does this match your expected output?

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Anonymous
Not applicable

@v-kkf-msft  thanks for the reply.Much appreciated.

 

If we do concatenation with "%" then it would become string right.

So as i mentioned in my post, it can be used only in table visual or matrix visual but it cant be used in other visuals like line chart or bar chat pie chart.

 

Jus to clarify you, i am not getting any error while trying above things which i mentioned.

My only concern here is, if i select a measure for example "Revenue" which is currency formated.

Now all the calculation items in that group shows the currency format only untill and unless if we mention a specific format for that calculation item in "FORMAT STRING" option.

If we do use FORMAT STRING option and mention a specific format, then it causing performance issue.

So by avoiding the concatenations, format string option, or FORMAT function, we are trying to figure out a solution.

Now client is agreed to show the values without %, and jus to show in one decimal or two decimal for the Diff% calculation item.

So that it can be used in other visuals also.

 

Let me know if you need any further details.

 

Thanks,

Mohan V

 

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.