DAX to show average to reach version n for each quotation

[mehaboob557]

Hi All,

I want to get reports in two scenarios,

             1. average versions sent to each quotateNO to reach version <1.0 

             2. average versions sent to each quotateNO to reach 2.0(i.e versions which are greaterthan(>)1.0 and lessthan(<)2.0 

example:

  1. lessthan(<) 1.0 versions

               quoteNo   |   versions

                  abc1             0.1

                  abc2             0.6

                  abc3             0.2

Total            3                0.9

=======================

Avergae version = divide( total version, total count of quotations)

                          =  0.9/3

same for 2nd scenario

Please help me how can i write dax formula  to achieve this.

you can find sample data in the dropbox url https://www.dropbox.com/s/ml1bbmbgfjz664y/avg%20versns.csv?dl=0

Please sugegst me or send me any pbix file by using my csv sample data.

[mehaboob557]

Hi,

I have created two measure.

1st measure:

 total sum avg = CALCULATE(SUM('version'), ALLSELECTED('quoteNo'))

2nd measure:  

avg versions final = DIVIDE([total sum avg], DISTINCTCOUNT('prod_check dw_proposal'[quoteNo]))

Finally I got the required average verions.

[v-shex-msft · Accepted solution]

Hi @mehaboob557,

Please check below measure formulas if they suitable for your requirement.

avg versions final <1 = 
DIVIDE (
    CALCULATE ( SUM ( 'avg versns'[version] ), 'avg versns'[version] < 1 ),
    CALCULATE ( DISTINCTCOUNT ( 'avg versns'[quoteNo] ), 'avg versns'[version] < 1 ),
    0
)

avg versions final 1~2 = 
DIVIDE (
 CALCULATE (
 SUM ( 'avg versns'[version] ),
 AND ( 'avg versns'[version] > 1, 'avg versns'[version] < 2 )
 ),
 CALCULATE (
 DISTINCTCOUNT ( 'avg versns'[quoteNo] ),
 AND ( 'avg versns'[version] > 1, 'avg versns'[version] < 2 )
 ),
 0
)

Regards,

Xiaoxin Sheng