Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Matrix visual

Hi All, My scenario is to visualize the availability % of the Products in each level of hierarchy in a matrix visual. In which I have to show  % value as 0 even if any one of the job failed to run o...
Need Help
Tutorial Requests
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    Hi,  Anonymous ;

    You could try ISINSCOPE() , Here I create a simple file and i create a measure.

    Measure = IF(ISINSCOPE('Table'[Date]),IF(SUM('Table'[job faild])>0,0,AVERAGE('Table'[job faild])),AVERAGE('Table'[job faild]))

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    3 years ago

    This is perfect! Got the required output. Thankyou so much v-yalanwu-msft.

    Appreciate your support.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi v-yalanwu-msft

 

In the above result, the weekly drill-up level must have the average of the daily percentage i.e. average of July 20 - July 24 must be count(jobsfailed=0)/number of dates in the week which is [1/5]. Here the '0' will be no job failure and this shows the product is 100% available. This also has a condition where the average value must be based on the count of 0 job failures. This must be the same for the other drill-up levels like month, quarter, year, product, project.

 

I have tried the below formula to acheive the same. But somehow the output value happen to be wrong.

 

avg_test =
Var avtest = CALCULATE(AVERAGE(Prod_Jobs_Snow[IsJobFailed]),
ALLEXCEPT(Prod_Jobs_Snow, Prod_Jobs_Snow[Project], Prod_Jobs_Snow[Product], Dim_Date[Year], Dim_Date[Quarter], Dim_Date[Month], Dim_Date[Week of Month], Dim_Date[Date]))
Return
IF(Prod_Jobs_Snow[IsJobFailed] = 1, avtest, 0).

 

Would really appreciate if I could get a help on this. Thanks in advance.