Need Help to get resolve on multiple queries of Custom KPI creation

[Anonymous]

Hello All,

 I am trying to create kpi's like showing UP and Down arrows for different kind of data.

But facing multiple issues.

I need your help to reolve these issues.

1:-  Cant Show Images of UP and Down arrows while creating KPI.

Table is

Measure for KPI creation is,

Measure = IF(ISBLANK([PlanSum]),"No Data Available ",[PlanSum])&" "&IF([PlanSum]=[PlanPrevMon],"",IF([PlanSum] > [PlanPrevMon],UNICHAR(8679),UNICHAR(8681))&IF([PlanSum]<=0,"",""))

And this gives me perfect result(please suggest me if ay better way for this).

But when i tried to show UP and DOWN image arrows instead of unichar arrows i cant.

Measure is

Measure = IF(ISBLANK([PlanSum]),"No Data Available ",[PlanSum])&" "&IF([PlanSum]=[PlanPrevMon],"",IF([PlanSum] > [PlanPrevMon],"https://cdn3.iconfinder.com/data/icons/musthave/48/Stock%20Index%20Up.png","https://cdn3.iconfinder.com/data/icons/musthave/48/Stock%20Index%20Down.png")&IF([PlanSum]<=0,"",""))
Output is 

How do i can get the Image arrow.Or else is there any other way to do this.

2:- Same queries But not working in persentage values.

Table

For Caculating the previus month values

Measure:-

Contri Prev Mon = CALCULATE([Contri%],PREVIOUSMONTH('Month Year'[Date]))

But the values are changed to decimal.

Why am i getting the values in decimals here.

If i changed that Contri% to decimals and do the rest of queries like

Measure:- 

ContriArrows = IF(ISBLANK([Contri%]),"No Data Available ",[Contri%])&" "&IF([Contri%]=[Contri Prev Mon],"",IF([Contri%] > [Contri Prev Mon],UNICHAR(8679),UNICHAR(8681))&IF([Contri%]<=0,"",""))

And the output is 

Fine its perfect.

but same above, if i include the UP and Down arrows images URL's it gives same output as Q1.

So how can i show the values in % and with the Up and Down arrows.

And for this contri% arrows kpi i have written another calculated column,

ContriKeys1 = SWITCH(
			TRUE(),
				[Contri Prev Mon]=BLANK(),"",
				[Contri%] <= 0,"",
				[Contri%] > [Contri Prev Mon],"https://cdn3.iconfinder.com/data/icons/musthave/48/Stock%20Index%20Up.png",
				[Contri%] < [Contri Prev Mon],"https://cdn3.iconfinder.com/data/icons/musthave/48/Stock%20Index%20Down.png",
				[Contri%] = [Contri Prev Mon],"")

But it giving me the wrong arrow symbol

Why am i getting those wrong arrow symbols here.

Please Suggest me.

Mohan V

[SevenD]

@Anonymous

Hi,

About question 1: The measures you called look more like Calculated column. You can't concatenate any string with a URL, or the URL will be treated as a string. Make a little change of your formula, it will work. Change the data category to "image URL".

ImageColumn =
IF (
    ISBLANK ( [PlanSum] ),
    "No Data Available ",
    IF (
        [PlanSum] = [PlanPrevMon],
        BLANK (),
        IF (
            [PlanSum] > [PlanPrevMon],
            "https://cdn3.iconfinder.com/data/icons/musthave/48/Stock%20Index%20Up.png",
            "https://cdn3.iconfinder.com/data/icons/musthave/48/Stock%20Index%20Down.png"
        )
    )
)

 

[Anonymous]

Thanks for the reply @SevenD

I did tried before itself what you have suggested and i have shown in my question itself that i have used this calculated column. You can see in last question.

I can show those arrow sysmbols in table but i cant in cards.you have suggested me the same thing that i have posted in my question.

I would like to clarify one more time what i was expecting to get in cards.

Measure for up and down arrows in cards visualization.

PlanArrows1 = 
IF (
    ISBLANK ( [PlanSum] ) || [PlanSum]<=0,
    "Data Not Available",[PlanSum])&
    IF ( 
	[PlanSum]=[PlanPrevMon],
	"",
	IF (
	    [PlanSum] > [PlanPrevMon],UNICHAR(9650),
		UNICHAR(9660))
)

It works perfect as in below image

But when i use url insted of those UNICHAR values in measure it wont work as it is taking it as string.

PlanArrows1 = 
IF (
    ISBLANK ( [PlanSum] ) || [PlanSum]<=0,
    "Data Not Available",
    IF ( 
	[PlanSum]=[PlanPrevMon],
	"",
	IF (
	    [PlanSum] > [PlanPrevMon],
	    "https://cdn3.iconfinder.com/data/icons/musthave/48/Stock%20Index%20Up.png",
            "https://cdn3.iconfinder.com/data/icons/musthave/48/Stock%20Index%20Down.png"
	)
	)

Output

What you have suggested is a calculated column which shows up and down arrows if the values are greater or less than the previuos month values in cards visualization.

I need to show the cards visualization as above shown in images that values with up and down arrows.

[SevenD]

@Anonymous

Cards visualization doesn't support images. Have a look at: https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-3-9-create-gauges-cards/. Maybe you can try Multi-row Card.

[v-shex-msft]

Hi @Anonymous,

Current, power bi not support dynamic calculated column based on slicer or other similar filter visual. (calculate result of measures which put in calculate column will different from original measures)

In my opinion, you can add a test calculate column to output the result of current measure calculation.

For example:

Test Result = "Contri% :"&[Contri%]&", Contri Prev Mon :"&[Contri Prev Mon]

If the result are different from result in table visual, I'd like to suggest you use character arrow as the custom kpi icon.

Regards,

Xiaoxin Sheng

[Anonymous]

Thanks for the reply @v-shex-msft.

Onething i would like to make sure is, I dont want to use slicer or any filter visuals further anymore.

I would like to write a measure or calculated ciolumn which can comapre curent month value and the previous month value.

If the previuos month value is not available then it should compare with THE previous month value.

As per my understaning you have suggested me to check the result of measure or calculated columns into another test calculated column, so that, we can know what exactly the output we are getting.

Thanks for the suggestion.

Output of the measure you have suggested is

But i didnt understood how does this can help my issue to get resolve.

Can you please elaborate.

Please dont hesitate to suggest.

Thanks,

Mohan V

[v-shex-msft]

Hi @Anonymous,

The calculation in calculated column is different than measure, you can find out the difference between original measure and the test column.

The if statement will based on the result of calcualte column, so your visual will show the wrong icon.

As I mentioned, if you want create a dynamic arrow icon, it will be suitable to use measure with character arrow.

Regards,

Xiaoxin Sheng