cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
johnfa Frequent Visitor
Frequent Visitor

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 PeriodProductSales
01/01/20191A0
01/01/20192A0
01/01/20193A0
01/01/20191B3
01/01/20192B1
01/01/20193B3
01/01/20191C0
01/01/20192C1
01/01/20193C2
02/01/20191A0
02/01/20192A0
02/01/20193A0
02/01/20191B0
02/01/20192B0
02/01/20193B0
02/01/20191C0
02/01/20192C0
02/01/20193C0
03/01/20191A1
03/01/20192A0
03/01/20193A3
03/01/20191B2
03/01/20192B2
03/01/20193B0
03/01/20191C1
03/01/20192C3
03/01/20193C2


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/20191
02/01/20192
03/01/20190

 

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
YJ Regular Visitor
Regular Visitor

Re: Measure to sum another measure problem

Hi ,

Under Modeling , New Table put this in:

20191022_BI_johnfa.JPG

 

 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 
02/01/20192
is not correct, it should be 3. but let me know.
 
If you need a 0 on the missing dates, link it to a date table or left join it.
 
Regards
 

View solution in original post

Community Support Team
Community Support Team

Re: Measure to sum another measure problem

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

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

6 REPLIES 6
YJ Regular Visitor
Regular Visitor

Re: Measure to sum another measure problem

Hi ,

Under Modeling , New Table put this in:

20191022_BI_johnfa.JPG

 

 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 
02/01/20192
is not correct, it should be 3. but let me know.
 
If you need a 0 on the missing dates, link it to a date table or left join it.
 
Regards
 

View solution in original post

Community Support Team
Community Support Team

Re: Measure to sum another measure problem

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

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

johnfa Frequent Visitor
Frequent Visitor

Re: Measure to sum another measure problem

Thanks 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 Regular Visitor
Regular Visitor

Re: Measure to sum another measure problem

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-dax

groupBy_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 filter
name: "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

 

20191023_BI.JPG

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

 

johnfa Frequent Visitor
Frequent Visitor

Re: Measure to sum another measure problem

Thanks for taking the time to explain it fully thats really helpful. Being new to DAX it is certainly easier to follow if every step is broken down explicitly into a separate table although obviously a bit long winded.   It is frustrating that you cant refer to columns created in the inner table in the outer table in the same block ie "Products_w_no_Sales" -  I am used to working with SQL where you can easily create aliases for datasets and columns and refer to them later.   Would it be possible to assign the inner table to a variable and then use that later to refer to all the columns we want ?

YJ Regular Visitor
Regular Visitor

Re: Measure to sum another measure problem

Hi, @Jo 

afaik, its is not yet possible to declare global var in DAX:

https://community.powerbi.com/t5/Developer/Is-it-possible-to-have-global-variable-for-whole-report/t...

 

However for improve readaiblity, its possible to have  local var:

Tb_Products_w_no_Sales =

Var

InnerTable= SUMMARIZECOLUMNS(Table1[Date ],Table1[Product],"ZERO_S",IF(SUMX(Table1,Table1[Sales])=0,0,1)

Return

SUMMARIZECOLUMNS(Table1[Date ],

(Filter (InnerTable),[ZERO_S]=0)),

"Products_w_no_Sales",

DISTINCTCOUNT(Table1[Product])

)
 
Of course with the indentation in powerbi it should look clearer there.( i am sorry that i didnt indent it well )
I understand how you feel , for something similiar to SQL, (use VAR as above)
WITH MYTABLE 
AS
() 
SELECT *
FROM BASETABLE
 
but for alias , SELECT * from BASETABLE as ALIAS_MYTABLE, i think there is nothing  similiar...
 
My personal preference when working with Powerbi and SQL is to have a view or table function on the SQL server and have powerbi query from that to do measures, YTY , simple agregation and reporting. But i am sure this is subjective 😃
 
regards
 
 
 

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,851)