Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Top1 for YTD

Hello there guys,

 

I struggle with some measures when I'm using Top 1 for MS, YTD and MAT.

 

I have build the following measure for finding Top1 value (max Total Value) based on my Product-Market dimension. 

 

EvoValues_Top1 = maxx(TOPN(1,SUMMARIZE('dw PHR_OTC_FACT','dw Product'[MARKET],"Sales",SUM('dw PHR_OTC_FACT'[Values_CPP])),[Sales],DESC), [Sales])

 

 

 

Everyting it's ok and works as a charm for MS. (Monthly Sale), but when I'm trying to do the same for MAT and YTD, the result is the same as for MS.

 

Bellow you can see the measures:

 

- YTD measure
Total Values YTD CY = TOTALYTD([Total Values],'DateTable'[Date])

- YTD top1
EvoValues_Top1 YTD = maxx(TOPN(1,SUMMARIZE('dw PHR_OTC_FACT','dw Product'[MARKET],"Sales", TOTALYTD(SUM('dw PHR_OTC_FACT'[Values_CPP]),'DateTable'[Date])),[Sales],DESC), [Sales])

AND

- MAT measure 
MAT CY = 
    CALCULATE( [Total Values],
        DATESINPERIOD( 'DateTable'[Date], LASTDATE( 'DateTable'[Date] ), -1, YEAR )
    )

- MAT top1 
EvoValues_Top1 MAT = maxx(TOPN(1,SUMMARIZE('dw PHR_OTC_FACT','dw Product'[MARKET],"Sales", CALCULATE( SUM('dw PHR_OTC_FACT'[Values_CPP]),
        DATESINPERIOD( 'DateTable'[Date], LASTDATE( 'DateTable'[Date] ), -1, YEAR )
    )),[Sales],DESC), [Sales])

What I did wrong, I tried several times with different measures...

Thank you in advance!

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    First of all, you should NEVER use SUMMARIZE to do calculations inside it. If you want to know why, please go to www.sqlbi.com and find the article(s) about it by The Italians. SUMMARIZE can only be used to do grouping, no calculations.

     

    So, your measure should look like this:

     

     

    EvoValues_Top1 =
    var __table =
        ADDCOLUMNS(
            SUMMARIZE (
                'dw PHR_OTC_FACT',
                'dw Product'[MARKET]
            ),
            "_Sales_",
                -- the SUM should be made into its own measure
                -- so that CALCULATE is not needed in this place
                CALCULATE(
                    SUM ( 'dw PHR_OTC_FACT'[Values_CPP] )
                )
        )
    var __maxSales =
        MAXX (
            TOPN (
                1,
                __table,
                [_Sales_]
            ),
            [_Sales_]
        )
    return
        __maxSales

    Secondly:

     

     

    -- YTD measure
Total YTD CY =
var __ytd =
	CALCULATE(
		[Total Values],
		DATESYTD( 'DateTable'[Date] ) -- must be a proper Date table
	)
return
	__ytd

    Thirdly:

     

    EvoValues_Top1 YTD =
var __table =
	ADDCOLUMNS(
	    SUMMARIZE (
	        'dw PHR_OTC_FACT',
	        'dw Product'[MARKET]
	    ),
            "_Sales_YTD_",
                    -- Why don't you use the measure from above - [Total YTD CY]?
                    -- Measures, once defined, should be reused as much as possible. 
        	CALCULATE(
            	    SUM ( 'dw PHR_OTC_FACT'[Values_CPP] ),
            	    DATESYTD( DateTable[Date] )
               )
	)
var __maxYtd =
	MAXX (
	    TOPN (
	        1,
	        __table,
	        [_Sales_YTD_]
	    ),
	    [_Sales_YTD_]
	)
return
	__maxYtd

    For this to work, DateTable needs to be a proper Date table marked as such and connected to the fact table in a 1:many fashion.

     

    Best

    Darek

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    First of all, you should NEVER use SUMMARIZE to do calculations inside it. If you want to know why, please go to www.sqlbi.com and find the article(s) about it by The Italians. SUMMARIZE can only be used to do grouping, no calculations.

     

    So, your measure should look like this:

     

     

    EvoValues_Top1 =
    var __table =
        ADDCOLUMNS(
            SUMMARIZE (
                'dw PHR_OTC_FACT',
                'dw Product'[MARKET]
            ),
            "_Sales_",
                -- the SUM should be made into its own measure
                -- so that CALCULATE is not needed in this place
                CALCULATE(
                    SUM ( 'dw PHR_OTC_FACT'[Values_CPP] )
                )
        )
    var __maxSales =
        MAXX (
            TOPN (
                1,
                __table,
                [_Sales_]
            ),
            [_Sales_]
        )
    return
        __maxSales

    Secondly:

     

     

    -- YTD measure
Total YTD CY =
var __ytd =
	CALCULATE(
		[Total Values],
		DATESYTD( 'DateTable'[Date] ) -- must be a proper Date table
	)
return
	__ytd

    Thirdly:

     

    EvoValues_Top1 YTD =
var __table =
	ADDCOLUMNS(
	    SUMMARIZE (
	        'dw PHR_OTC_FACT',
	        'dw Product'[MARKET]
	    ),
            "_Sales_YTD_",
                    -- Why don't you use the measure from above - [Total YTD CY]?
                    -- Measures, once defined, should be reused as much as possible. 
        	CALCULATE(
            	    SUM ( 'dw PHR_OTC_FACT'[Values_CPP] ),
            	    DATESYTD( DateTable[Date] )
               )
	)
var __maxYtd =
	MAXX (
	    TOPN (
	        1,
	        __table,
	        [_Sales_YTD_]
	    ),
	    [_Sales_YTD_]
	)
return
	__maxYtd

    For this to work, DateTable needs to be a proper Date table marked as such and connected to the fact table in a 1:many fashion.

     

    Best

    Darek

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you for your fast and great response.

      That worked smoothly. :)

      I tried so many options and the only one worked was that with SUMMARIZE (only for MS) that's why I tried to use it for other 2 too.

      Again, thankx and have a great day!

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous 

    Sample data and expected output will be helpful to provide an accurate solution.If you need further help,please follow the How to Get Your Question Answered Quickly to post your simple assumed data and expected output.You can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

    Regards,