# DAX formula/measure problem

### Question

• Dear excel champions

My problem is to find the DAX formula decribing distribution ratio.
Distribution ratio should be calculated as: (based on data below)

Distribution ratio= avg(Number of Visits Where product is available in ALFA stores / Number of Visits where product should be available in ALFA stores , Number of Visits Where product is available in BETA stores / Number of Visits where product should be available in BETA stores) = avg(4/8,3/4) = 62.5%

The fact which complicate this calculation is ALFA stores visited more than once.

I tried everything and could not find the solution.
I hope someone manage to help me with this task

Table. 1

Client    Product ID          Visit date            Number of Visits where              Number of Visits Where

product should be available          product is available

ALFA     aaa                         02.12.2013          1

ALFA     bbb                        02.12.2013          1                                                            1

ALFA     ccc                          02.12.2013          1                                                            1

ALFA     ddd                        02.12.2013          1                                                            1

ALFA     aaa                         15.12.2013          1

ALFA     bbb                        15.12.2013          1                                                            1

ALFA     ccc                          15.12.2013          1

ALFA     ddd                        15.12.2013          1

BETA     aaa                         16.12.2013          1                                                            1

BETA     bbb                        16.12.2013          1                                                            1

BETA     ccc                          16.12.2013          1

BETA     ddd                        16.12.2013          1                                                            1

Anyone helps? What function should I use: calculate, average, averagex, summarize, filter, count, countx, earlier, sumx, discount, countrows?
I do not know which ones are appropriate and how to join them in a one formula/measure.

thx for any posts

Saturday, February 01, 2014 12:36 PM

• Great job Marco :)

It' working!

In a final formula we need to replace divided sums:

avg :=
AVERAGEX (
VALUES ( Tablename[Client] ),
CALCULATE (
SUM ( [Number of Visits where product is available] )
/  SUM ( Tablename[Number of Visits where product should be available] )
)
)

Sunday, February 02, 2014 11:24 AM

### All replies

• Probably you need to write this:

avg :=
AVERAGEX (
VALUES ( Tablename[Client] ),
CALCULATE (
SUM ( Tablename[Number of Visits where product should be available] )
SUM ( [Number of Visits where product is available] )
)
)

If the two SUM are already defined in a measure, you can remove CALCULATE and SUM.

Marco Russo
http://www.sqlbi.com
http://www.daxformatter.com

Sunday, February 02, 2014 6:20 AM
• Great job Marco :)

It' working!

In a final formula we need to replace divided sums:

avg :=
AVERAGEX (
VALUES ( Tablename[Client] ),
CALCULATE (
SUM ( [Number of Visits where product is available] )
/  SUM ( Tablename[Number of Visits where product should be available] )
)
)