Firstsortedvalue - calculation

[jerryr125]

Hi -

I am trying to calculate the total number of quantity by the last date by state.

1. I created the following variable:

vquantitylocation

=IF(LEN(Category)>0,location)

** This is created so that it removes any values from my data in which the Category is Null.

2. I create the following master measure:

firstsortedvalue(Aggr(sum(quantity),$(vquantitylocation) ,-date)

Does not work - any thoughts ?? Thanks - Jerry

[tresesco]

I got your message and app. It seems the main issue is - granularity, at level you are trying to aggregate the measures. Instead of If(Sum(... you should try with Sum(If.. . I have made changes to first two KPIs you have in your app.

Try like:

Sum(If(LEN(POPESTIMATE2020)>0,POPESTIMATE2020)) 

Sum({<[date.autoCalendar.Date]={'$(=Max([date.autoCalendar.Date]))'}>}people_vaccinated)

[jerryr125]

Hi - Thank you again so much for your help.

Almost there - I need to check to see if the state code has text (characters).

I tried this:

Sum({<[date.autoCalendar.Date]={'$(=Max([date.autoCalendar.Date]))'},istext(left[State Code],1)>}people_vaccinated)

No luck - thoughts  ?

[jerryr125]

Ok - still trying to make progress on this - almost there.

I changed the master measure to the following:

IF((POPESTIMATE2020)>0,SUM({<[date.autoCalendar.Date]={'$(=Max([date.autoCalendar.Date]))'}>}people_fully_vaccinated),NUM('0'))

As a result:

This works

Notice : American Samoa column for 'Fully Vaccinated YTD' is 0 - which is correct.

BUT now when  go to do view the KPI:

The KPI for Fully Vaccinated (YTD) it displays zero.

If I filter on one state it works.

Any thoughts ? Jerry

[tresesco · Accepted solution]

That is because, you are making the same mistake, using if(sum( .... structure instead of sum(if(... (I suggested above). 

Expression to be tried:

SUM({<POPESTIMATE2020={">0"},[date.autoCalendar.Date]={'$(=Max([date.autoCalendar.Date]))'}>}people_fully_vaccinated)

Please close the thread by liking and/or marking correct answer. That would help others as well. 

[jerryr125]

Thank you again so much for your help and dedication

Yes, this is correct :

SUM({<POPESTIMATE2020={">0"},[date.autoCalendar.Date]={'$(=Max([date.autoCalendar.Date]))'}>}people_fully_vaccinated)