Speed of distribution

[answeriver]

Hi all,

Please tell me can i make report of distibution new product with DAX formula?

I have data with sales, date, outlets, cities, products. 

So i need to made report of counting outlets which purchase one of product and show it information by cities and weeks.

Early i made it in excel with manual calculation, but now i need made it for many products separate from each other.

[v-yulgu-msft]

Hi @answeriver,

I am not clear about your requirement. Please follow this blog to provide sample data and show us your desired output.

Regards,

Yuliana Gu

[answeriver]

Hi Yuliana,

Actually i aldready made report by this function:

Running Total Distr = CALCULATE(DISTINCTCOUNT(IMS[SALEPOINT CODE]);FILTER(ALLSELECTED(IMS);IMS[VISIT DATE]<=MAX(IMS[VISIT DATE])))

And have this results:

WEEK NUM	Runnunig Total Disrt
38	345
39	662
40	779
41	835
42	852
43	875
44	1016
45	1279
46	1326
47	1371
48	1419

Sorry i can't insert image? i dont know how.

But when i try add split by cities, function not working.

[v-yulgu-msft]

Hi @answeriver,

I noticed that you calculate running total value based on "VISIT DATE" column, but you didn't add this column into visual, instead you put "WEEK NUM". Is this expected? Besides, if adding "City" column into visual, what the desired result should be?

Regards,

Yuliana Gu

[answeriver]

Dear Yuliana,

About week num it's ok, calculating working. On the image you can see running total for all country, but in my data have cities.

I want count outlets which purchase product by cities with cumulative count.

[v-yulgu-msft]

Hi @answeriver,

Maybe you can try this:

Running Total Distr =
CALCULATE (
    DISTINCTCOUNT ( IMS[SALEPOINT CODE] );
    FILTER (
        ALLSELECTED ( IMS );
        IMS[CITY] = SELECTEDVALUE ( IMS[CITY] )
            && IMS[VISIT DATE] <= MAX ( IMS[VISIT DATE] )
    )
)

For more advice, you'd better post sample data in source table and show us the desired output that shows running total values with weeknum and cities in a single visual.

Best regards,

Yuliana Gu

[answeriver]

Dear Yuliana,

Your solution is not working

That what i want to see

 How i can add file?

[affan]

Hi @answeriver

You can upload your pbix file to any file hosting site like dropbox or google and share the link here.

[answeriver]

Dear Affan,

Here is link to data file https://drive.google.com/open?id=1jl09nqLZHCCfcYwf8ULm1cK9wSMcAlCt.

[affan]

Hi @answeriver

Please confirm if you are looking for a result similar to below shown 

Regards

Affan

[answeriver]

Hi Affan, Do you have some news?

[answeriver]

Dear Affan,

Thanks for your time.

https://drive.google.com/open?id=1320oYQ3Jdm304zAkAvggurkHwcTNm1nf

Here is my manual calculation for report, like my way. What i want is max of final results what i can get with DAX.