count the instances returned by a measure

[dustin_advmgt]

Is there any way to create a measure to count the number of instances returned by another measure?

[dustin_advmgt]

I have built a sales forecast report using the following measures:

Sales (Prior Day) = SUMX(CALCULATETABLE(SOP30300,DATEADD(ALL(SOP30200[GLPOSTDT]),-1,DAY),ALL(SOP30200[GLPOSTDT])),SOP30300[Sales])

Sales (Prior Month) = SUMX(CALCULATETABLE(SOP30300,DATEADD('Date'[Date],-1,MONTH),ALL(SOP30200[GLPOSTDT])),SOP30300[Sales])

Sales (Prior Week) = SUMX(CALCULATETABLE(SOP30300,DATEADD('Date'[Date],-7,DAY),ALL(SOP30200[GLPOSTDT])),SOP30300[Sales])

Sales (Prior Year) = SUMX(CALCULATETABLE(SOP30300,DATEADD('Date'[Date],-1,YEAR),ALL(SOP30200[GLPOSTDT])),SOP30300[Sales])

Theses measures are used in the following measures to forecast sales:

Sales Forecast = ([Sales (Prior Day)]+[Sales (Prior Week)]+[Sales (Prior Month)]+[Sales (Prior Year)])/(IF(ISBLANK([Sales (Prior Day)]),0,1)+IF(ISBLANK([Sales (Prior Week)]),0,1)+IF(ISBLANK([Sales (Prior Month)]),0,1)+IF(ISBLANK([Sales (Prior Year)]),0,1))

The measures work very nicely in a clustered bar chart and the forecast amount is accurate when compared with prior months and years.

However, I would like to use the gauge visual to show our progress against the forecast in total. When using the [Sales Forecast] measure in this context, the result is less than the amount that should be displayed. Mathmatically, this is due to the divisor evaluating to 4 when it should be a percentage of 4. The percentage can be calculated by dividing the number of instances in [Sales (Prior Month)] by the number of instances in [Sales (Prior Year]).

Is there a better way to arrive at the amount needed for the gauge visual or how can I calculate the number of instances in each of the aforementioned measures?

[dustin_advmgt]

I have built a sales forecast report using the following measures:
 
Sales (Prior Day) = SUMX(CALCULATETABLE(SOP30300,DATEADD(ALL(SOP30200[GLPOSTDT]),-1,DAY),ALL(SOP30200[GLPOSTDT])),SOP30300[Sales])
 
Sales (Prior Month) = SUMX(CALCULATETABLE(SOP30300,DATEADD('Date'[Date],-1,MONTH),ALL(SOP30200[GLPOSTDT])),SOP30300[Sales])
 
Sales (Prior Week) = SUMX(CALCULATETABLE(SOP30300,DATEADD('Date'[Date],-7,DAY),ALL(SOP30200[GLPOSTDT])),SOP30300[Sales])
 
Sales (Prior Year) = SUMX(CALCULATETABLE(SOP30300,DATEADD('Date'[Date],-1,YEAR),ALL(SOP30200[GLPOSTDT])),SOP30300[Sales])
 
The measures are used in the following measure to forecast sales:
 
Sales Forecast = ([Sales (Prior Day)]+[Sales (Prior Week)]+[Sales (Prior Month)]+[Sales (Prior Year)])/(IF(ISBLANK([Sales (Prior Day)]),0,1)+IF(ISBLANK([Sales (Prior Week)]),0,1)+IF(ISBLANK([Sales (Prior Month)]),0,1)+IF(ISBLANK([Sales (Prior Year)]),0,1))
 
The measures work very nicely in a clustered bar chart and the forecast amount is accurate when compared with prior months and years. However, I would like to use the gauge visual to show our progress against the forecast in total. When using the [Sales Forecast] measure in this context, the result is less than the amount that should be displayed. Mathematically, this is due to the divisor evaluating to 4 when it should be a percentage of 4. The percentage can be calculated by dividing the number of instances in [Sales (Prior Month)] by the number of instances in [Sales (Prior Year]).
 
Is there a better way to arrive at the amount needed for the gauge visual or how can I calculate the number of instances in each of the aforementioned measures?

[v-frfei-msft]

Hi @dustin_advmgt,

Could you please share you sample data and excepted result to me? And you can use rankx and max function to have a try.

Regards,

Frank

[Ashish_Mathur]

Hi,

Generically speaking, this should work

=COUNTROWS(measure)

To get more specific help, describe the question, share some data and show your expecred result.