Dynamic Measure help

[vjnvinod]

Dear all,

Below is my table visual in power BI desktop.

I am looking for a measure which can calculate the desire count based on multiplying"Target" to "Total of Projected FY21 Closing HC"

for eg

5.50%*1494=82.17 (See below the output table as well)

please note that Target is a coloumn and "Total of Projected FY21 Closing HC" is Measure in my model

Sample pbix

https://drive.google.com/file/d/109YRdkB2STFy4w7DADMqtsJresM5SACs/view?usp=sharing

Expected Output table

Rank	Projected FY21 Closing HC	Target	Desired Count
Partner/Principal	60	5.50%	82.17
Executive Director	34	11.00%	164.34
Senior Manager	163	3.30%	49.302
Manager	150	16.50%	246.51
Senior	723	33.00%	493.02
Staff/Assistant	364	30.70%	458.658
	1494		1494

[Anonymous]

Try this measure

Desired Count =
VAR Tgt =
    SUMX ( Rank_Target, Rank_Target[Target] )
VAR PFY21HC =
    CALCULATE ( [Projected FY21 Closing HC], ALLSELECTED ( Rank_1[Rank_new] ) )
VAR Result = PFY21HC * Tgt
RETURN
    Result

please note that ALLSELECTED() is used to ensure the following behaviour in case of any filter/slicer is applied on Rank outside this visual.

[vjnvinod]

@Anonymous 

thanks for this, but i am getting the below error

[Anonymous]

You have missed the 1st line. The first line should be the measure name =

But you have missed the first line and entered the measure as...

That is why you are getting the error.

[vjnvinod]

@Anonymous 

thank you, but for some reason the 2nd VAR PFY21HC is not giving the right output.

i have to tweak your measure like below

But the problem is that i have to hard code the total

Desired Count =

VAR Tgt =

SUMX ( Rank_Target, Rank_Target[Target] )

VAR PFY21HC =

1494

VAR Result = PFY21HC * Tgt

RETURN

Result

[vjnvinod]

@Anonymous 

These are the output picture when 

[Anonymous]

I tested it in your sample pbix file and it is giving the correct output. If you are using it in any other pbix file, possibly with different table names or field names, you have to change it accordingly. Hard-coding the value 1494 is not a good practice. Moreover, you have any filters or slicers applied, then the hard-coded value of 1494 will remain unchanged and it is likely to give you wrong results. Refer to the image below...

[vjnvinod]

@Anonymous 

This is looking fine, not sure why its not working in my pbix

can you share the pbix, let me compare that, thank you for your help

[Anonymous]

I did nothing to your pbix other than adding a new measure as mentioned in the previous post and adding that measure to the visual. Try doing the same. There is no reason for it to give wrong results. It is a simple measure. I think there is a difference between the sample file you have sent and the actual file on which you are working. Try to find the difference and fix it. I would like to mention again that "hard-coding" is not a good idea.

[vjnvinod]

@Anonymous 

Actually i looked at again, there is nothing changed in my pbix

I did a retest, with the 2nd part of the formula, instead of 1494 across all ranks, its splitting the numbers hence the output is incorrect, see below

there is something which is not working, any idea what it could be?

[Anonymous · Accepted solution]

For the following formula to work properly,

Desired Count =
VAR Tgt =
    SUMX ( Rank_Target, Rank_Target[Target] )
VAR PFY21HC =
    CALCULATE ( [Projected FY21 Closing HC], ALLSELECTED ( Rank_1[Rank_new] ) )
VAR Result = PFY21HC * Tgt
RETURN
    Result

The rank field used in the visual should be the same as the rank field used within the 

ALLSELECTED ( Rank_1[Rank_new] ) )

ALLSELECTED part of the formula. Here we have used the the field from Rank_1 table. But if you use the rank from some other table in the visual, it will not give the correct results.

For example, take a look at the image below.

The first table is correct and the 2nd table is wrong because the rank field used in the visual is different from what is passed to ALLSELECTED()