Forum Discussion

bullius's avatar
bullius
Helper V
9 years ago

Using Running Total in Calculations

Hi, I have a measure that calculates the running total of no. of companies. I want to multiply this figure for a given year by another amount, e.g.    Year Running Total Amount Desired Res...
Need Help
bullius's avatar
bullius
Helper V
9 years ago

Dim_Calendar is connected to Fact_OccasionsComp. So, if I filter Dim_Calendar, it filters Fact_OccasionsComp, which affects the running total formula because I am using ALLSELECTED, rather than ALL. However, when I use ALL, it bypasses the other filters I have in the formula, ie,

Fact_OccasionsComp[CompOccasion] = "Established"
Fact_OccasionsComp[CompOccasion] = "Closed",
	Fact_OccasionsComp[Date] = null

which changes the result.

If you can think of a way to do this using a measure, that would be useful still, but I think I am going to resort to creating a separate table summarizing the running total by year, then use that to calculate my "Desired Result" column.

 

Thanks for your help!

 

 

 

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

Hi bullius,

 

I wonder if "Allexcept" would help. Maybe you could try this one if no other filter exists.

Running Total =
CALCULATE (
    DISTINCTCOUNT ( Fact_OccasionsComp[CompanyID] ),
    Fact_OccasionsComp[CompOccasion] = "Established",
    FILTER (
        ALLEXCEPT ( Fact_OccasionsComp, Fact_OccasionsComp[CompOccasion] ),
        Fact_OccasionsComp[Date] <= MAX ( Dim_Calendar[Date] )
    )
)
    - CALCULATE (
        DISTINCTCOUNT ( Fact_OccasionsComp[CompanyID] ),
        Fact_OccasionsComp[CompOccasion] = "Closed",
        FILTER (
            ALLEXCEPT ( Fact_OccasionsComp, Fact_OccasionsComp[CompOccasion] ),
            Fact_OccasionsComp[Date] <= MAX ( Dim_Calendar[Date] )
                && Fact_OccasionsComp[Date] = null
        )
    )

Best Regards!

Dale

  • bullius's avatar
    bullius
    Helper V
    9 years ago

    v-jiascu-msft - good suggestion. I haven't used that function before.

     

    Unfortunately, it didn't work in this case. I added all the columns I wanted to be able to filter, excluding the date column. The problem is if I exclude the date column, I only get one result, regardless of the year. And if I include the date column as one I want to be able to filter, we're back to the original problem. 

     

    Thanks!

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      9 years ago

      Hi bullius,

       

      Maybe it's a good idea to create a new measure.

       

      Best Regards!

      Dale

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        9 years ago

        Hi bullius,

         

        Could you please tell me if your problem was resolved? Could you please share the answer or mark the proper answer if it's convenient for you? That will be a help to the others.

        Best Regards!
        Dale