Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate SUMX & VALUES & Filter EARLIER

Hi I have a table Projectshistorical with severall ProjectIds in it.

projectdetails.projectIdrepositories.repo.locrepositories.repo.scantimestampRollProjectLoc
36925812.04.2019 09:1869258
32238012.04.2019 09:1969258+22380
36925812.04.2019 09:3769258+22380
36925812.04.2019 09:5069258+22380
32238012.04.2019 09:5469258+22380
313602312.04.2019 09:5569258+22380+136023
32238012.04.2019 10:0669258+22380+136023
313602312.04.2019 10:1069258+22380+136023
 
RollProjectLoc = CALCULATE(
SUMX(
VALUES('Projectshistorical'[projectdetails.projectId]);[repositories.repo.loc]);
FILTER('Projectshistorical';'Projectshistorical'[repositories.repo.scantimestamp]<=EARLIER(Projectshistorical[repositories.repo.scantimestamp])))
 
And it always gives me the answer that it cannot find the repositories.repo.loc if I create a quick measure it tells me that this is a ring dependency...  How do I make this work?
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    Please check the sample pbix as attached. If it doesn't meet your requirement, kindly share your excepted result to me.

     

    Measure = 
VAR cal =
    CALCULATETABLE (
        DISTINCT ( 'Projectshistorical'[repositories.repo.loc] ),
        FILTER (
            ALLSELECTED ( Projectshistorical ),
            'Projectshistorical'[repositories.repo.scantimestamp]
                <= MAX ( 'Projectshistorical'[repositories.repo.scantimestamp] )
        ),
        VALUES ( Projectshistorical[repositoryId] )
    )
RETURN
    SUMX ( cal, 'Projectshistorical'[repositories.repo.loc] )

     

     

7 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous ,

     

    To create a measure as below.

     

    Measure = 
VAR cal =
    CALCULATETABLE (
        DISTINCT ( 'Table1'[repositories.repo.loc] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            'Table1'[repositories.repo.scantimestamp]
                <= MAX ( 'Table1'[repositories.repo.scantimestamp] )
        )
    )
RETURN
    SUMX ( cal, 'Table1'[repositories.repo.loc] )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-frfei-msft 

      Wow this looks great and very promising, but it seems not to take into account that I also need to filter on the project ID but instead filters on distinct values of repo.loc. What can happen is that two repos are used in different projects or that two repos in the same project have the same repo.loc. In my table I have also different projects, what I wrote in the text, what was probably a bit misleading. Should have made more examples in the table. My apologies.

      So I tried to make it Distinct by project ID but then it actually multiplies repo.loc with the project.id. Do you know how I can make it work?

      RollingLOC = 

VAR cal =
    CALCULATETABLE (
        DISTINCT ( 'Projectshistorical'[projectdetails.projectId]);
        FILTER (
            ALLSELECTED ( Projectshistorical );
            'Projectshistorical'[repositories.repo.scantimestamp]
                <= MAX ( 'Projectshistorical'[repositories.repo.scantimestamp] )
        )
    )
RETURN
    SUMX ( cal; 'Projectshistorical'[repositories.repo.loc])
      Thanks a lot in advance. This is completely different from how I tried to do that. 
      • v-frfei-msft's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi Anonymous ,

         

        Update the formula as below.

         

        Measure =
VAR cal =
    CALCULATETABLE (
        DISTINCT ( 'Table1'[repositories.repo.loc] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            'Table1'[repositories.repo.scantimestamp]
                <= MAX ( 'Table1'[repositories.repo.scantimestamp] )
        ),
        VALUES ( 'Projectshistorical'[projectdetails.projectId] )
    )
RETURN
    SUMX ( cal, 'Table1'[repositories.repo.loc] )

         

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous ,

     

    Please check the sample pbix as attached. If it doesn't meet your requirement, kindly share your excepted result to me.

     

    Measure = 
VAR cal =
    CALCULATETABLE (
        DISTINCT ( 'Projectshistorical'[repositories.repo.loc] ),
        FILTER (
            ALLSELECTED ( Projectshistorical ),
            'Projectshistorical'[repositories.repo.scantimestamp]
                <= MAX ( 'Projectshistorical'[repositories.repo.scantimestamp] )
        ),
        VALUES ( Projectshistorical[repositoryId] )
    )
RETURN
    SUMX ( cal, 'Projectshistorical'[repositories.repo.loc] )

     

     

  • marab's avatar
    marab
    New Member
    6 years ago

    Hi all, 

    I have a problem with the formula below that should be correct but it doesn't recongnize the column name in the EARLIER function. 

    I simply need to sum up the Item Value per each project name like:

     

    Project Name           Item            Item Value                Project Value

    A                                 1                       50                               89

    A                                 2                       39                               89

    B                                 1                       10                               100

    B                                 2                       50                               100

    B                                 3                       40                               100

     
    Project Value = Sumx(FILTER('Tab_Project','Tab_Project'[Project Name]=EARLIER('Tab_Project'[Project Name],'Tab_Project'[Item Value]))
     
    Any clue? 
    a million thanks
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      6 years ago

      Hi,

      If you want a calculated column formula, then try this

      =calculate(sum('Tab_Project'[Item Value]),FILTER('Tab_Project','Tab_Project'[Project Name]=EARLIER('Tab_Project'[Project Name])))

      If you want a measure, then try this

      =calculate(sum('Tab_Project'[Item Value]),allexcept('Tab_Project','Tab_Project',['Tab_Project'[Item]]))

      Hope this helps.