Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Running Count Percentage of Total Optimization Issues

Hello,

 

I have a rather large dataset that I am trying to make a graph visualization for. I'm currently stuck creating the running count percentage column with a measure. Here's what I have so far:

 

 

Measure 3 = 
    CALCULATE (
       [Measure],            
        FILTER (
             ALLSELECTED('Cases'[DaysToReport] ),
            'Cases'[DaysToReport] <= MAX ( 'Cases'[DaysToReport] )
        )
    )

 

 

This formula works, but is incredibly time intensive. [Measure] is just the caseid divided by total case ids. DaystoReport is the time aspect in this graph. I've tried including [Measure] inside Measure 3 but it doesn't output correctly. 

 
 

That red line is what the output of the visual should look like. Unfortunately Im not at liberty to give the data but CaseID is just a long number and daystoreport is a summation (I'm not sure what the basis of the summation is). 

Need Help

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      The quick measure version of a running total is what we originally started with. It works, but the run time is too long. I need to somehow reduce calculations and get more of it to run in the storage engine. Here are the two codes I have currently (that run at about 50% of the speed we want):

      Measure = 
DIVIDE (
    COUNTA('Cases'[CaseId]),
    CALCULATE ( COUNTA('Cases'[CaseId]), ALLEXCEPT ('Cases','Cases'[CaseId]) )
)
      CALCULATE (
       [Measure],            
        FILTER (
             ALLSELECTED('Cases'[DaysToReport] ),
            'Cases'[DaysToReport] <= MAX ( 'Cases'[DaysToReport] )
        )
    )

      This combination runs at about half the time that the following code does:

      PercentofCasesR-TAT =
 DIVIDE(CALCULATE(DISTINCTCOUNT('Cases'[CaseId]), FILTER('Cases', 'Cases'[DaysToReport])), CALCULATE(DISTINCTCOUNT('Cases'[CaseId]), 'Cases'[DaysToReport] <= 999999))
      RunningTotalPercentageofCasesR-TAT = 
CALCULATE(
	[PercentofCasesR-TAT],
	FILTER(
		ALLSELECTED('Cases'[DaysToReport]),
		ISONORAFTER('Cases'[DaysToReport], MAX('Cases'[DaysToReport]), DESC)
	)
)

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        This is the solution I was thinking of. This code works, but doesn't output the correct graph. It doesn't output a running total percent but instead just pure count of caseid. v-chuncz-msft 

        Measure 6 = 
VAR distinctidcount =
CALCULATE(DISTINCTCOUNT('Cases'[CaseId]))
VAR rankedcount =
RANKX(ALL('Cases'), SUMX(RELATEDTABLE('Cases'), [DaysToReport])) 
VAR maxrank = 
MAX(ALLSELECTED('Cases'[CaseId]),rankedcount) 
VAR alldistinct =
COUNTROWS(ALL('Cases'[CaseId]))
VAR calkulation =               
FILTER (
    ALLSELECTED('Cases'[CaseId] ),
    rankedcount <= maxrank)
RETURN
CALCULATE(distinctidcount/alldistinct,calkulation)