Average over the fiscal year not working.

[SwapnilS]

Hi, I am calculating a value called platform availability based on down time and total minutes in a month. So platform availability = Divide ([Total Down Time] / [Total minutes in month]). Total minutes in month = (total number of clients * minutes in month).
Now I am calculating the average of platform availability over Fiscal Quarter and then over Fiscal year. The value is correct at the quarter level, but it does not return correct number at yearly level. Can some please help.

Below is the fake sample data

Platform Availability =

var Downtimevalue = VALUE( Uptime1[Outage Length (Minutes)])

var TotalDownTime = IF( Downtimevalue = 0, 0, CALCULATE([Total Down Time],

ALLEXCEPT( 'Uptime1', Uptime1[Fiscal QY] )

)

)

var MaxClientonCloud =

CALCULATE(

AVERAGE( Uptime1[Max Clients on Cloud] ),

ALLEXCEPT( 'Uptime1', 'Uptime1'[Fiscal QY] )

)

var MinsinMonth = 43920

var TotalClientMinutes = MaxClientonCloud * MinsinMonth

var Numerator = TotalDownTime

Var Denominator = TotalClientMinutes

var Result = 1- (DIVIDE(Numerator , Denominator))

Return

IF( TotalDownTime = 0,

1, Result

)

[v-shex-msft · Accepted solution]

HI @SwapnilS,

Calculate columns are different as a measure formula, they are matched with different row content levels.

Calculated Columns and Measures in DAX - SQLBI
For this scenario, I think you need to use the earlier function to manually setting filter ranges based on the current field value instead of on a specific category and its filter effect. (calculate column and measure are work on different data level)

How To Use The EARLIER Function In Power BI - A DAX Tutorial | Enterprise DNA

If you confused about the coding formula, please share a pbix file with some dummy data and your expressions to test.

How to Get Your Question Answered Quickly 

Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

[SwapnilS]

@amitchandak This has not worked. Basically what I want is sum of the numbers at the quarterly level and  divide it by 4. Just showing that in excel.

[v-shex-msft · Accepted solution]

HI @SwapnilS,

Calculate columns are different as a measure formula, they are matched with different row content levels.

Calculated Columns and Measures in DAX - SQLBI
For this scenario, I think you need to use the earlier function to manually setting filter ranges based on the current field value instead of on a specific category and its filter effect. (calculate column and measure are work on different data level)

How To Use The EARLIER Function In Power BI - A DAX Tutorial | Enterprise DNA

If you confused about the coding formula, please share a pbix file with some dummy data and your expressions to test.

How to Get Your Question Answered Quickly 

Notice: the data level of power bi.

Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

Regards,

Xiaoxin Sheng

[SwapnilS]

Hi, 

Thank you for the solution. Yes this has worked.

Thank you for the support.

[SwapnilS]

@v-shex-msft 

Thank you for the reply.

I will try your suggestions today. I am facing some issues with my laptop. Once done I will confirm.

[v-shex-msft · Accepted solution]

Hi @SwapnilS,

Sure, I hope these links help with your scenario.
BTW, if you confused about the coding formula, you can also share a pbix file with some dummy data that keeps the raw table structures to test.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[SwapnilS]

@amitchandak First of thank you for looking into this, since I am trying this for almost a week. The formulae I pasted above is for a calculated column. This formulae is returning correct number at quarterly level. I have another calculated table, I will try your solution now and let you know.

[amitchandak]

@SwapnilS , I think this because of var MaxClientonCloud =
CALCULATE(
AVERAGE( Uptime1[Max Clients on Cloud] ),
ALLEXCEPT( 'Uptime1', 'Uptime1'[Fiscal QY] )
)

it allow filter for Qtr

try like

// Add year too

var MaxClientonCloud =
CALCULATE(
AVERAGE( Uptime1[Max Clients on Cloud] ),
ALLEXCEPT( 'Uptime1', 'Uptime1'[Fiscal QY], , 'Uptime1'[Fiscal FY] )
)