Getting a Count of all None Blank Values in a Measure Column in my Table

[julesdude]

Hi all,

I've the following Matrix Table in my report:

I want to put in a card the total percentage of the percentages here. So for the above, this would be 40%.

My issue is, the Occupancy column here is a measure which references the Leased Area column, also a measure.

My current measure did work, however, I put in some additional logic that returns zeros in the Leased Area column and these are ignored. I want it to count the zeros as part of the _count that divides the _sum. If there are blank entries, this should not be part of the _count.

Current measure (Not working):

Total Occupancy = 
var _sum = SUMX(ALLSELECTED(Lease_Unit),[Occupancy])
VAR _count = COUNTROWS(FILTER(ALLSELECTED(Building),[Occupancy]<>BLANK()))
return DIVIDE(_sum,_count)

This returns 100% as the _count totals 2 and the _sum totals 2,00

How can the mesaure be corrected?

[johnt75 · Accepted solution]

Try

Total Occupancy =
VAR _sum =
    SUMX ( ALLSELECTED ( Lease_Unit ), [Occupancy] )
VAR _count =
    COUNTROWS ( FILTER ( ALLSELECTED ( Building ), NOT ISBLANK ( [Occupancy] ) ) )
RETURN
    DIVIDE ( _sum, _count )

If you use the non-equality operator 0 equates to BLANK.

[julesdude]

Perfect. Thanks @johnt75 and yes it did indeed work! Just a simple tweak!

I see that 

ISBLANK ( [Occupancy] ) = FALSE

also works., but not my original:

,[Occupancy]<>BLANK()

I'll note this down in future! Thanks again!

[julesdude]

Hi @johnt75 

I wondered if you could help me with a very similar issue. Same report and matrix table as above and same problem - using a different column I'm trying to have the total display in a card visual.

The column looks like this:

Valuation
40300000
51500000
89100000
33200000
8391044
33685201
17279634
52150000
19843731
11331221
40750000
36600000
64000000
19992267
20300000
72722763
50100000
44500000
72600000
52100000
23301084
19400637

The card should be displaying the total of this column as 873.14M but instead it displays 908.77M

The measure for the card is:

Total Valuation = SUMX(Building, [Valuation to Fund Currency (Last Date)])

The measure providing values in the column of the matrix table:

Valuation to Fund Currency (Last Date) = 
// CONVERT THE NATIVE CURRENCY AMOUNT TO THE FUND CURRENCY 
// Get user selected currency
var selectedcurrency = [var FundCurrency]

// Get native currency
var origincurrency = [var origincurrency]

// Get last date asset was valued
var dateofassetevaluation = LASTDATE('Asset_Valuation'[Valuation Date])

// Look for last date of valuation and return nearest preceding date available for currency converstion in adjacent column [ECBRateDate] in the DataTable table
var ecbdateforevaluation = LOOKUPVALUE(DateTable[ECBRateDate],DateTable[Date],dateofassetevaluation)

// Now return the conversion rate value [OBS_VALUE] in the ECB_FX_RATES table where [CURRENCY] matched origincurrency and [CURRENCY_DENOM] is EUR and date is the same [TIME_PERIOD] = ecbdateforevaluation
var closest_to_date = CALCULATE(MAX(DistinctECBRates[TIME_PERIOD]), DistinctECBRates[TIME_PERIOD] <= dateofassetevaluation, ECB_FX_RATES[CURRENCY] = origincurrency)
var currency_value_closest_to_date = CALCULATE(SUM(ECB_FX_RATES[OBS_VALUE]), ECB_FX_RATES[TIME_PERIOD] = closest_to_date && ECB_FX_RATES[CURRENCY] = origincurrency)

// LOOKUPVALUE(ECB_FX_RATES[OBS_VALUE],ECB_FX_RATES[CURRENCY],origincurrency,ECB_FX_RATES[CURRENCY_DENOM],"EUR",ECB_FX_RATES[TIME_PERIOD],ecbdateforevaluation)

// Now calculate the conversion rate - if the origincurrency is EUR, just use the [ValueLastDate SUM] value otherwise if it's different divide the [ValueLastDate SUM] with currency_value_closest_to_date converstion rate
var euro_value = if(origincurrency="EUR",[Valuation Total TABLE SUM],[Valuation Total TABLE SUM]/currency_value_closest_to_date)

// Get the fund currency rate by finding the currency out of EUR to selected currency - return the rate [OBS_VALUE] in ECB_FX_RATES table by matching [CURRENCY] with the selectedcurrency & [CURRENCY_DENOM] = EUR and [TIME_PERIOD] = ecbdateforevaluation (the date of the valuation)
var closest_to_date2 = CALCULATE(MAX(DistinctECBRates[TIME_PERIOD]), DistinctECBRates[TIME_PERIOD] <= dateofassetevaluation, ECB_FX_RATES[CURRENCY] = selectedcurrency)
var desired_currency_value_closest_to_date = CALCULATE(SUM(ECB_FX_RATES[OBS_VALUE]), ECB_FX_RATES[TIME_PERIOD] = closest_to_date2 && ECB_FX_RATES[CURRENCY] = selectedcurrency )

// var desired_currency_value_closest_to_date = LOOKUPVALUE(ECB_FX_RATES[OBS_VALUE],ECB_FX_RATES[CURRENCY],selectedcurrency,ECB_FX_RATES[CURRENCY_DENOM],"EUR",ECB_FX_RATES[TIME_PERIOD],ecbdateforevaluation)

// If selected currency is EUR, use the euro_value amount as no second stage conversion needed, otherwise use the euro_value * desired_currency_value_closest_to_date to convert to selected currency. If the calculation returns an error, return conversion amount as 0
var actual_value = IFERROR(if(selectedcurrency="EUR",euro_value,euro_value* desired_currency_value_closest_to_date),0)

RETURN
IF (NOT origincurrency = "EUR", actual_value * [Ownership Percentage], actual_value)

I am not sure if the Building table should be referenced in my measure for this. For the table visual, I am applying a filter from the filter pane to just that visual:

I am not sure if this is the cause. Asset is in Portfolio is a measure I've created. I cannot transfer it to the card visual or apply it to the 'Filters on this page', so I am not sure if I need to use KEEPFILTERS in some way or that I may in fact be using the incorrect table Building in my SUMX for this. Any ideas on what I might be doing wrong?

[johnt75]

The key question is whether or not the Building expanded table will filter the fund currency and origin currency measures, and the asset valuation table. 

One way to check the intermediate results would be to use DAX Studio to pick a unique column from Building and run the calculation against that, e.g.

EVALUATE
SUMMARIZECOLUMNS (
    'Building'[Building ID],
    "Valuation", [Valuation to Fund Currency (Last Date)]
)

and then compare the results with what you expect to see.