none
calculated measure = sum of a row level number divided by sum of a summary row level number RRS feed

  • Question

  • Hi , I just set up an ssas (2017 std) server and deployed a tabular model from vs2019.  Long term I wont be doing this but for the time being my fact table looks like what's in the code block and my dax calc'd measure needs to be ...

    Efficiency:=sum([SoldStd])/sum of distinct IdealSoldStd for these sales identifier and fk location combo. 

    How would/could that denominator look in dax?

    CREATE TABLE [star].[FactSales](
    	[Id] [bigint] IDENTITY(1,1) NOT NULL,
    	
    	[SoldSTD] [float] NULL,
    	[IdealSoldSTD] [float] NULL,
    	[sales identifier] int,
    	[fk_location] [smallint] NULL,
    	[fk_dim2] [int] NULL,
    	[fk_dim3] [int] NULL,
    	[fk_dim4] [int] NULL,
    	[fk_dim5] [int] NULL,
    	[fk_date] [date] NULL,
     CONSTRAINT [PK_FactSales] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

     
    • Edited by db042190 Thursday, October 17, 2019 2:47 PM these makes more sense than this in calc description
    Thursday, October 17, 2019 2:04 PM

Answers

  • i think I've got it. my numerator issue is not an issue as my qa against sql was joining an incorrect dim 5 table. so these expressions work.

    Since my expressions work, i'm not going to try to figure out the problems with Will's expression.  Will's expression doesn't work at least in my situation. And it sends ssas into a tailspin when an attempt is made to turn it into a calc'd measure.  I suspect using calc'd tables directly in a dax denom sum uses the calc'd table "as is" instead of dynamically generating/filtering it in the context of fk_location and sales identifier established in the numerator.   I cant even guess what the tailspin was all about when i tried to turn Will's recommendation into a calc'd measure.  Maybe the community can speak to both of those questions. 

    This seems to work...

    Numerator:=sum([SoldSTD])

    Denominator:=sumx(summarize('star FactSales',[fk_location],[sales identifier],"IdealSoldSTD"

    ,max('star FactSales'[IdealSoldSTD])),[IdealSoldSTD])

     Efficiency:=divide([Numerator],[Denominator])
    Monday, October 21, 2019 1:13 PM

All replies

  • Hi 

    Please find the below DAX which simulates your requirement.

    Average = SUM(TableName[SoldStd])/COUNTROWS(GROUPBY(TableName,TableName[IdealSoldStd],TableName[fk location]))

    Hope this is helpful !!

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Thursday, October 17, 2019 2:20 PM
  • thx nkumar but this isn't an average problem.

    The sum of the relevant values from the table below somehow needs to be looked up in the denominator. 

    I have to admit I forget the difference between max and maxx so its possible I meant maxx.   And its possible distinct would work instead of max.

    any fk location and identifier combo  included in the numerator  has to be included in the denominator as a distinct IdealSoldSTD value included in a sum. 

    evaluate summarize
    ('star FactSales',
    [fk_location],
    [sales identifier],
    "IdealSoldSTD",MAX('star FactSales'[IdealSoldSTD]))

    Thursday, October 17, 2019 2:54 PM
  • I tried this but got the error "the expression specified in the query is not a valid table expression"

    evaluate 
    sumx(
    summarize
    ('star FactSales',
    [fk_location],
    [sales identifier],
    "IdealSoldSTD",MAX('star FactSales'[IdealSoldSTD])
    ),
    "IdealSoldSTD")

    Thursday, October 17, 2019 3:10 PM
  • apparently evaluate can only return a table.

    anyway I put that expression in as the denominator (in vs) of my calc and now wonder how I can be sure that only the population of data points sliced in the numerator will be included in the denominator?

    Thursday, October 17, 2019 3:33 PM
  • Hi db042190,

    Thanks for your reply.

    Per your recent replies, you need to create a calculated table at first. The DAX expression for that calculated table is like this.

    CalculatedTable1:=summarize
    ('star FactSales',
    [fk_location],
    [sales identifier],
    "IdealSoldSTD",MAX('star FactSales'[IdealSoldSTD])
    )
    

    Then the final measure "Efficiency" could be written like this.

    Efficiency:=sum('star FactSales'[SoldStd])/sum('CalculatedTable1'[IdealSoldSTD])  

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by db042190 Friday, October 18, 2019 11:05 AM
    • Unmarked as answer by db042190 Friday, October 18, 2019 11:39 AM
    Friday, October 18, 2019 6:37 AM
  • thx Will, it looks like both ways are valid, ie just putting your calc'd table in the denominator behind a sum also works...i'm assuming for now the context of both numerator and denom will be the same and satisfy any filters placed on them.


    • Edited by db042190 Friday, October 18, 2019 11:06 AM grammar and afterthought
    Friday, October 18, 2019 11:05 AM
  • it looks like these two approaches (efficiency and efficiency 2) are generating different answers. I caught this by using SSMS's browsing capability over the model.  I'm going to look at the sql and report back here which is correct.  Overall they are giving the same answer but sliced I saw big differences.

    [Efficiency Actual]:=sum([SoldSTD])

    Efficiency Ideal:=sumx(summarize('star FactSales',[fk_location,[sales identifier],"IdealSoldSTD"

    ,max('star FactSales'[IdealSoldSTD])), 

    [IdealSoldSTD])

    Efficiency:=divide([Efficiency Actual],[Efficiency Ideal])

    Efficiency 2:=divide([Efficiency Actual],sum('CalculatedTable1'[IdealSoldSTD]))


    • Edited by db042190 Friday, October 18, 2019 11:54 AM additional info
    Friday, October 18, 2019 11:51 AM
  • to summarize, I checked excel against the ssms cube browser and both seem to yield the same behavior and results.

    I sliced way down to 35 records in sql to compare the data source rows against the model's aggregrations.  There are only 2 sales identifiers in this population si1 and si2.

    "Efficiency" is only aggregating in both the numerator and denominator across si2 which is the higher of the two si's numerically.--that's a problem.  I'm going to see if I can find the si1 data either in the vs grid or thru an evaluate statement.

    "Efficiency 2"'s denominator is always across the full population , ie si1,si2,si3....si n.  that's a problem when slicers are in effect.   the fact that it matches sql's sum of distinct values across everything makes me think i'll find the missing population of data from problem #1 when I go looking for it.  Unless perhaps the calculated table and original fact table make their way up to the server with discrepancies between the two.

    This is odd and hard to explain.  Till I realized Will's approach was always yielding the same result in the denominator, I created a calc'd measure called WillsDenom: = sum('calculatedtable1'[idealsoldstd]).  And tried to slice it along with everything else.  This sent ssas into a tail spin.  No more high performance pivoting results like I was having up to that point.  ssms reported it had to stop because it didn't have enough memory for the operation.  I think excel aborted.  

    Friday, October 18, 2019 2:05 PM
  • I ran an evaluate of the form you see below and believe ssas is seeing all 35 records.   since I was filtering on fiscal year and quarter name I double checked that the dim dates you see have their fiscal year and quarter name attributes set the same way.  its not clear to me why efficiency actual and efficiency ideal are accounting for only si2 when efficiency actual doesn't even care about sales identifiers.  I lost access temporarily but my plan is to delete the databases (the workspace db seems to hang around even after I close ssdt) , redeploy, slice again and if problem is still there see if I can find si1 in a pivot where ssas shows me the dims involved with those records.

    EVALUATE
    CALCULATETABLE (
    'star FactSales',
    'star FactSales'[fk_location]=1 ,
    'star FactSales'[fk_dim2]=102 ,
    'star FactSales'[fk_dim3]=652,
    'star FactSales'[fk_dim4]=11095,
    or('star FactSales'[fk_dim5] =696449,'star FactSales'[fk_dim5] =696566),
    or('star FactSales'[fk_date]=datevalue("6/20/2019"),'star FactSales'[fk_date] =datevalue("7/29/2019"))
    )
    


    • Edited by db042190 Friday, October 18, 2019 7:01 PM clarity
    Friday, October 18, 2019 6:49 PM
  • Are you self-discussing here? :-) Not sure which scenario you have come across.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 21, 2019 5:40 AM
  • Hi Will. no just posting problems as they occur. Looking to community for help.

    First issue is that the expression you recommended for denominator  is calculating over everything always (entire population of data), not just the current context.

    Second is that my numerator seems to be ignoring some data (si1) that I know is in the model having looked at the grid and having looked at an evaluate statement's resultset.  I'm looking closer at that today having just deleted and redeployed.

    Third, i tried making your recommendation a calc'd measure so perhaps it would calculate in the context of the current filters and it went nuts.  Never coming coming back.  aborting and complaining of memory limitations in ssms.

    Monday, October 21, 2019 11:37 AM
  • i think I've got it. my numerator issue is not an issue as my qa against sql was joining an incorrect dim 5 table. so these expressions work.

    Since my expressions work, i'm not going to try to figure out the problems with Will's expression.  Will's expression doesn't work at least in my situation. And it sends ssas into a tailspin when an attempt is made to turn it into a calc'd measure.  I suspect using calc'd tables directly in a dax denom sum uses the calc'd table "as is" instead of dynamically generating/filtering it in the context of fk_location and sales identifier established in the numerator.   I cant even guess what the tailspin was all about when i tried to turn Will's recommendation into a calc'd measure.  Maybe the community can speak to both of those questions. 

    This seems to work...

    Numerator:=sum([SoldSTD])

    Denominator:=sumx(summarize('star FactSales',[fk_location],[sales identifier],"IdealSoldSTD"

    ,max('star FactSales'[IdealSoldSTD])),[IdealSoldSTD])

     Efficiency:=divide([Numerator],[Denominator])
    Monday, October 21, 2019 1:13 PM
  • Yes, it depends on your business design. Since your solution could work, please kindly close the thread by marking your reply as answer.

    Thanks for your sharing.

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 22, 2019 7:26 AM