Forum Discussion

mgrayTCB's avatar
mgrayTCB
Helper IV
9 months ago
Solved

Different Calc for not IsInScope - Help

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

 

Need Help

10 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    9 months ago

    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-Forum/ba-p/963216
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • Shahid12523's avatar
    Shahid12523
    Community Champion
    9 months ago

    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
    )

  • danextian's avatar
    danextian
    Super User
    9 months ago

    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.

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    9 months ago

    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.

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    9 months ago

    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's avatar
      v-ssriganesh
      Community Support
      9 months ago

      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's avatar
        mgrayTCB
        Helper IV
        9 months ago

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