cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MohanV
Helper III
Helper III

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 @MohanV ,

 

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.

 

@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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!