none
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

    • Edited by Gordonik Saturday, February 01, 2014 12:59 PM
    Saturday, February 01, 2014 12:36 PM

Answers

  • 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] )
        )
    )

    Simple but made me frustrating

    • Marked as answer by Gordonik Sunday, February 02, 2014 11:24 AM
    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] )
        )
    )

    Simple but made me frustrating

    • Marked as answer by Gordonik Sunday, February 02, 2014 11:24 AM
    Sunday, February 02, 2014 11:24 AM