Different Calc for not IsInScope - Help

[mgrayTCB]

I have a matrix visual working how I want for row total to calculate something different than the sum of the rows for each year but in the total column on the total row I want the average of the numbers to the left - not the count of the rows.

See below - I want to replace the x with the average of 1,1,4,4,4, and 2 but nomatter what I do I can only get 5 which is the count of all the projects. Any pointers woudl be appreciated!

LifeCycleByCountOfMonths =

 var _tab =  

        SUMMARIZECOLUMNS(

            dimProject[ProjectName],dimCal[Period],

            "LCatTime",[LifeCycleByDate]

        )

var _selLifeCycle = SELECTEDVALUE('stbl-LifeAbrv'[LifeAbv])

VAR _countSelected = COUNTROWS(

            FILTER(_tab,[LCatTime] = _selLifeCycle))

var _prjs =  countx(

        SUMMARIZECOLUMNS(dimProject[ProjectName],FILTER(_tab,[LCatTime] = _selLifeCycle)),1)

var _aveProj = "x"

RETURN

SWITCH(TRUE(),  

        not(ISINSCOPE(dimProject[ProjectName]))  && not(ISINSCOPE(dimCal[year])) && not(ISINSCOPE(dimCal[Month])), _aveProj,

        not(ISINSCOPE(dimProject[ProjectName])),_prjs,

        _countSelected

    )

nicer formatting

[v-ssriganesh]

Hi @mgrayTCB,
To assist you further, could you please provide sample data that clearly illustrates the issue you're experiencing?

• Include a small dataset that fully captures the issue (preferably in table format or as a downloadable file, not just a screenshot).
• Avoid including any sensitive or unrelated information.
• Also share the expected outcome based on the sample data you provide.

Need help preparing or uploading sample data? You can refer to this helpful guide:
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

This will help us reproduce your scenario accurately and provide a precise solution.

Best regards,
Ganesh Singamshetty.

[v-ssriganesh]

Hello @mgrayTCB,

I am following up to see if you had a chance to review my previous response and provide the requested information. This will enable us to assist you further.

Thank you.

[mgrayTCB]

I took a break from trying to solve that problem. ill look at it with fresh eyes next week hopefully

[v-ssriganesh]

Hello @mgrayTCB,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

[v-ssriganesh]

Hello @mgrayTCB,

We hope you're doing well. Could you let us know if your issue has been resolved? If you are still experiencing any problems, please share sample data so we can reproduce the scenario and provide a solution.

Thank you.

[mgrayTCB]

I still cant get it to work. All I can get is the total count when what I want is the average of the count for each year.

Unfortunatly I dont have an easy way to share the data.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[danextian]

Hi @mgrayTCB 

You'll need to write a formula that will always return true at the grand total cell level and return the average of row total based on the year column. As requested by other users, please post post a sample data following the instructions provided.

[Shahid12523]

LifeCycleByCountOfMonths =
VAR _tab =
SUMMARIZECOLUMNS(
dimProject[ProjectName],
dimCal[Period],
"LCatTime", [LifeCycleByDate]
)
VAR _selLifeCycle = SELECTEDVALUE('stbl-LifeAbrv'[LifeAbv])
VAR _countSelected =
COUNTROWS(
FILTER(_tab, [LCatTime] = _selLifeCycle)
)
VAR _prjs =
COUNTX(
SUMMARIZECOLUMNS(dimProject[ProjectName], FILTER(_tab, [LCatTime] = _selLifeCycle)),
1
)
VAR _aveProj =
AVERAGEX(
VALUES(dimProject[ProjectName]),
CALCULATE(
COUNTROWS(
FILTER(_tab, [LCatTime] = _selLifeCycle)
)
)
)
RETURN
SWITCH(
TRUE(),
NOT(ISINSCOPE(dimProject[ProjectName])) &&
NOT(ISINSCOPE(dimCal[Year])) &&
NOT(ISINSCOPE(dimCal[Month])), _aveProj,

NOT(ISINSCOPE(dimProject[ProjectName])), _prjs,

_countSelected
)

[lbendlin · Accepted solution]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...