locked
Grand total in SSAS Tabular cube RRS feed

  • Question

  • do we have any setting in tabular cube to set measure as non aggregatable  similar to isAggregatable in multidimensional cube ?
    Monday, April 29, 2019 8:16 AM

All replies

  • Hi  Mukul,

    As I know, there is no properties of IsAggregatable in Tabular model. If you want to disable Grant total, I think you need to set this properties in BI tool(Excel, POWERBI). You could refer to Show or hide subtotals and totals in a PivotTable for details.

    Best Regards,
    Zoe Zhi


    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, April 30, 2019 2:30 AM
  • You don't have a setting, but you can achieve a similar thing using an if expression in your measure(s) with a pattern like the following. Which filter your measure for a specific value of Column1 if Column1 does not have a single value (ie. you are viewing the data at an aggregated level)

    IF( HASONEVALUE( Table1[Column1] )
       , SUM(Table1[Column2]
       , CALCULATE( SUM( Table1[Column2], Table1[Column1] = "First Value")
    )


    http://darren.gosbell.com - please mark correct answers

    Tuesday, April 30, 2019 3:55 AM
  • That would work only for the dimension used in the calculation what if user is using other dimension

    EX

    =IF(HASONEVALUE(DimPeriod[Period]),
       SWITCH(VALUES(DimPeriod[Period]),
         “Current”, [Sales],
         “MTD”, [Sales](DATESMTD(DimDate[Datekey])),
         “QTD”, [Sales](DATESQTD(DimDate[Datekey])),
         “YTD”, [Sales](DATESYTD(DimDate[Datekey])),
         “LastYear”, [Sales](DATEADD(DimDate[Datekey],-1,YEAR)),
         “PriorYearMTD”, [Sales](DATEADD(DATESMTD(DimDate[Datekey]),-1,YEAR)),
         “PriorYearQTD”, [Sales](DATEADD(DATESQTD(DimDate[Datekey]),-1,YEAR)),
         “PriorYearYTD”, [Sales](DATEADD(DATESYTD(DimDate[Datekey]),-1,YEAR)),
         BLANK()
        ),
       [Sales]  )
       
       for this calculation grand total should not show up as it doesn't make any sence but it sould act normal for other dimensions
    Tuesday, April 30, 2019 6:01 AM
  • That would work only for the dimension used in the calculation what if user is using other dimension

    EX

    =IF(HASONEVALUE(DimPeriod[Period]),
       SWITCH(VALUES(DimPeriod[Period]),
         “Current”, [Sales],
         “MTD”, [Sales](DATESMTD(DimDate[Datekey])),
         “QTD”, [Sales](DATESQTD(DimDate[Datekey])),
         “YTD”, [Sales](DATESYTD(DimDate[Datekey])),
         “LastYear”, [Sales](DATEADD(DimDate[Datekey],-1,YEAR)),
         “PriorYearMTD”, [Sales](DATEADD(DATESMTD(DimDate[Datekey]),-1,YEAR)),
         “PriorYearQTD”, [Sales](DATEADD(DATESQTD(DimDate[Datekey]),-1,YEAR)),
         “PriorYearYTD”, [Sales](DATEADD(DATESYTD(DimDate[Datekey]),-1,YEAR)),
         BLANK()
        ),
       [Sales]  )
       
       for this calculation grand total should not show up as it doesn't make any sence but it sould act normal for other dimensions
    It just depends on what logic you want to apply. If the dimension is not selected the logic falls through to the ELSE condition in your IF or SWITCH, you can then either return a specific value or a blank depending on your requirements. You did not specify what your specific requirements were, you just asked if it was possible, which it is.

    http://darren.gosbell.com - please mark correct answers

    Tuesday, April 30, 2019 11:02 AM