cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AFoulk
Helper II
Helper II

filter 0 only when no prior results

Table = FILTER( ADDCOLUMNS( DISTINCT( CROSSJOIN( FILTER( VALUES('Stores[Store]), 'Stores'[Store] <> BLANK() ),
                                                                                           FILTER( VALUES('SKU'[SKU]), 'SKU'[SKU] <> BLANK() ),
                                                                                           FILTER(VALUES(Dates[WE]), Dates[WE] >= TODAY()-(365) &&

                                                                                                                                        Dates[WE] < TODAY() ) ) ),
                                                     "Sales", CALCULATE( SUM('Sales'[Qty]),

                                                                                       'Sales'[PO #] <> 0,

                                                                                       'Sales'[bolInvoiceSent] = TRUE() ) ),
                           [Sales] > 0 )

 

Here is what I currently have to build my Measure table that I want to use for calculating Average Monthly Sales based on a rolling 12 month sales history, but I would rather filter only where the Sales is 0 if there was no previous sales yet, but to actually include the 0 if in a week following a prior week that had sales.

 

Then I want to take the sum of those sales and divide by the DistinctCount of the total WeekEndings (WE). That total will then be multiplied by 52 to make a year and divided back down by 12 to determine a rough monthly average.

 

So the help I am looking for is first, how do I only filter the 0 sales if no prior weeks had sales but still retain where 0 sales occured after any previous weeks did contain sales, then second how to get the DistinctCount to work based on this table? The DistinctCount only works on a ColumnName but doesn't allow you to start typing any measure to try and tell it what to count without referencing a physical table's Column which defeats the purpose of why I want to do the DistinctCount within this created measure table.

5 REPLIES 5
v-danhe-msft
Microsoft
Microsoft

Hi @AFoulk,

From your description, I could not figure out your data structure, could you please post me more pictures about your data information or share your pbix file if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Daniel,

What exactly do you mean by data structure?

Hi @AFoulk,

Could you please post me the sample data in your different tables or share the pbix file if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I make a small sample explaining what I want a DAX measure to calculate for Average Sales.

How do I attach it onto this post?

AFoulk
Helper II
Helper II

FILTER( ADDCOLUMNS( DISTINCT( CROSSJOIN( FILTER( VALUES('Stores[Store]), 'Stores[Store] <> BLANK() ),
                                                                              FILTER( VALUES('SKU'[SKU]), 'SKU'[SKU] <> BLANK() ) ) ),
                                       "Mnthly Avg Sales", CALCULATE( SUM('Sales'[Qty]), 'Sales'[PO #] <> 0,
                                                                                                                          'Sales'[bolInvoiceSent] = TRUE(),
                                                                                                                          'Sales'[WE] >= TODAY() - 365,
                                                                                                                          'Sales'[WE] < TODAY() +1  )
                                       / DISTINCTCOUNT( 'Sales'[WE])
                                       * 52
                                       /12),
             [Mnthly Avg Sales] > 0 )

 

Now I tried this and figured I would just reference the actual table for my results measure as typed below, but then the results populating onto my table/matrix are all the same total (not breaking down by product per branch servicing the stores). I have the SUMX calculating in a parameter for % change in sales to manually increase/decrease these needs total.

 

SUMX('Store Avg Monthly Sales', 'Store Avg Monthly Sales'[Mnthly Avg Sales] * (1 + 'Needs'[Needs Change Value] ))

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors