Forum Discussion
Measure to sum another measure problem
Hi there
I am new to DAX and having problems calculating a measure to sum another measure and was hoping someone could point me in the right direction. I have simplified the data below to show the problem.
| Date | Period | Product | Sales |
| 01/01/2019 | 1 | A | 0 |
| 01/01/2019 | 2 | A | 0 |
| 01/01/2019 | 3 | A | 0 |
| 01/01/2019 | 1 | B | 3 |
| 01/01/2019 | 2 | B | 1 |
| 01/01/2019 | 3 | B | 3 |
| 01/01/2019 | 1 | C | 0 |
| 01/01/2019 | 2 | C | 1 |
| 01/01/2019 | 3 | C | 2 |
| 02/01/2019 | 1 | A | 0 |
| 02/01/2019 | 2 | A | 0 |
| 02/01/2019 | 3 | A | 0 |
| 02/01/2019 | 1 | B | 0 |
| 02/01/2019 | 2 | B | 0 |
| 02/01/2019 | 3 | B | 0 |
| 02/01/2019 | 1 | C | 0 |
| 02/01/2019 | 2 | C | 0 |
| 02/01/2019 | 3 | C | 0 |
| 03/01/2019 | 1 | A | 1 |
| 03/01/2019 | 2 | A | 0 |
| 03/01/2019 | 3 | A | 3 |
| 03/01/2019 | 1 | B | 2 |
| 03/01/2019 | 2 | B | 2 |
| 03/01/2019 | 3 | B | 0 |
| 03/01/2019 | 1 | C | 1 |
| 03/01/2019 | 2 | C | 3 |
| 03/01/2019 | 3 | C | 2 |
I am trying to get a count of the number of products without any sales by date so my output would look like this
| Date | #Products With No Sales |
| 01/01/2019 | 1 |
| 02/01/2019 | 2 |
| 03/01/2019 | 0 |
I can create a measure on my Sales Data table that groups things by date and product returning 1 if there are no sales and 0 if there are sales for the day.
Missing Product Sales For Day =
IF (
CALCULATE (
SUM ( 'Sales'[Sales]),
ALL ( Sales[Period])
) = 0,
1,
0
)
I was then thinking i would just be able to sum these values up by day in another measure but i cannot reference the first measure in any subsequent measure without getting the following error.
Column 'Missing Product Sales For Day' in table 'Sales' cannot be found or may not be used in this expression.
What would be the simplest way to do this ? I think i can do it by creating more tables using Summarize but that seems a bit clunky
many thanks
Johnfa
Hi ,
Under Modeling , New Table put this in:
Tb_Products_w_no_Sales = SUMMARIZECOLUMNS(Table1[Date ],
(Filter (SUMMARIZECOLUMNS(Table1[Date ],Table1[Product],"ZERO_S",IF(SUMX(Table1,Table1[Sales])=0,0,1)),[ZERO_S]=0)),"Products_w_no_Sales",DISTINCTCOUNT(Table1[Product]))*overall idea, ZERO_S will give 0 for rows of products with no sales, and after do a distinct count on product
where Table1 is your data.This should be what you want. i believe
is not correct, it should be 3. but let me know.02/01/2019 2 If you need a 0 on the missing dates, link it to a date table or left join it.RegardsHi johnfa ,
We can create a calcualted column and new a measure based on it.
per product = CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', 'Table'[Date ] = EARLIER ( 'Table'[Date ] ) && 'Table'[Product] = EARLIER ( 'Table'[Product] ) ) )Measure = CALCULATE(DISTINCTCOUNT('Table'[Product]),FILTER('Table','Table'[per product] = 0))
6 Replies
YJResolver II
Hi ,
Under Modeling , New Table put this in:
Tb_Products_w_no_Sales = SUMMARIZECOLUMNS(Table1[Date ],
(Filter (SUMMARIZECOLUMNS(Table1[Date ],Table1[Product],"ZERO_S",IF(SUMX(Table1,Table1[Sales])=0,0,1)),[ZERO_S]=0)),"Products_w_no_Sales",DISTINCTCOUNT(Table1[Product]))*overall idea, ZERO_S will give 0 for rows of products with no sales, and after do a distinct count on product
where Table1 is your data.This should be what you want. i believe
is not correct, it should be 3. but let me know.02/01/2019 2 If you need a 0 on the missing dates, link it to a date table or left join it.RegardsThanks great that works - though not entirely sure what is happening ! Presumably the inner SUMMARIZECOLUMNS table gets evaluated first then the other columns are referring to the values returned from the inner table and not the base table Table1 ?
ie "Products_w_no_Sales" DISTINCTCOUNT(Table1[Product]) is working on the rows returned from the inner SUMMARIZECOLUMNS table or have i misunderstood ? . If so it would be nice if you could rename the columns to reflect where they are coming from but this does not seem possible ?. The column "ZERO_S" for example cannot be referenced outside the inner SUMMARIZECOLUMNS table.
- YJ
Thanks johnfa for accepting it as the solution and the kudo,
its ready great to try to understand what is happening, so lets try:
Presumably the inner SUMMARIZECOLUMNS table gets evaluated first then the other columns are referring to the values returned from the inner table and not the base table Table1 ? YES
ie "Products_w_no_Sales" DISTINCTCOUNT(Table1[Product]) is working on the rows returned from the inner SUMMARIZECOLUMNS table or have i misunderstood ? . YES , it is doing a distinct count on column of base table Table1[Product] after the inner summarise after the filter. Then we assign this new(final) agregation (which would be show to the users) as "Products_w_no_Sales". if you change "Products_w_no_Sales" to "PPP" you see "PPP" in your final table field.
https://docs.microsoft.com/en-us/dax/summarizecolumns-function-daxgroupBy_columnName: Table1[Date] ( thats why finally we see the rows for all our grouped(unique) dates)filterTable: the chuck of prehaps confusion which is your basetable after a 1st summarise follow by a filtername: "Products_w_no_Sales"expression: DISTINCTCOUNT(Table1[Product]The column "ZERO_S" for example cannot be referenced outside the inner SUMMARIZECOLUMNS table. It can be referenced outside the inner SUMMARIZECOLUMNS table but not outside the Tb_Products_w_no_Sales table(afaik)
Minus the issue of been chucky with a couple of uncessary tables, you can break it down in steps for understanding. And in this way if you need to refer to it outside the table, Example ZERO_S can now be refered elsewhere
TB_P1xx: Which in my opinion is the crucial part, it identifies wether each group have zero summation of the sales. the IF here is not absolute necessary, its more of to go along with your concept of "groups things by date and product returning 1 if there are no sales and 0 if there are sales for the day." although i just realised that i did the opposite ....=)
TB_P2xx: Filter only those with no sales
TB_P3xx: Count how many have no sales in each date group.
Hope it helps, let me know if you have more question or need the PBIX.
Regards
v-frfei-msftCommunity Support
Hi johnfa ,
We can create a calcualted column and new a measure based on it.
per product = CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( 'Table', 'Table'[Date ] = EARLIER ( 'Table'[Date ] ) && 'Table'[Product] = EARLIER ( 'Table'[Product] ) ) )Measure = CALCULATE(DISTINCTCOUNT('Table'[Product]),FILTER('Table','Table'[per product] = 0))