none
Dax For grouping

    Question

  • Hi I have following Data set as data Model.

    Country        City         AssetCount
    USA          Newyork        50
    USA         Washington    40
    USA         California        30

    India        Banglore      100
    India        Delhi             50
    India        Bombay        30

    I want to show one row showing count at country level & city level on the same row.
    There should two slicers for slicing City & Country as below.

        USA        Newyork        
        India      Washington    
                      California    
                      Banglore    
                      Delhi        
                      Bombay    

    So when I select Country as India it should count of Asset at Country(India) level.
    In the same way when I select City as Delhi it should count of Asset at City(Delhi) level.

           India      Delhi
    ----    180        50

    Is it possible using PowerPivot?

        
    Thursday, January 02, 2014 5:30 AM

Answers

  • ok, so you basically do not want so called Visual Totals - meaning that higher levels should only be aggregated by the visible descendants

    in order to accomplish this in DAX we need to check on which level we are (City or Country) and use different calculations:

    Value_City:=SUM('Query'[AssetCount])

    Value_Country:=CALCULATE(SUM('Query'[AssetCount]), ALLEXCEPT('Query', 'Query'[Country]))

    the tricky part comes when we try to find out, what is currently selected if you do your selection via a slicer
    usually you would use:

    Value:=IF(ISFILTERED('Query'[City]), [Value_City], [Value_Country])

    but the problem is that if you use your City-column as a slicer, ISFILTERED will always return true!
    to work around this issue you need two City-columns, one that is used in the hierarchy and one that is used as a slicer

    this is very similar to what Alberto describes here: http://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax

    hth,
    gerhard


    - www.pmOne.com -

    Thursday, January 02, 2014 1:16 PM

All replies

  • this is more related to the Pivot Table but not to Power Pivot

    and yes, it is possible
    you may need to create a hierarchy as Country-->City

    put it on rows and add Slicers for Country and City too
    this slicer will then restrict your Pivot Table to only show what is selected in the slicer

    hth,
    gerhard


    - www.pmOne.com -

    Thursday, January 02, 2014 10:40 AM
  • Actually your solution is not working. I have created the hierarchy as Country-->City & kept that in Rows. So when I select a particular Country & City it showing like this:

    Row Labels           AssetCount
    USA                          40
      Washington           40
    Grand Total              40

    But I want

    USA      Washington     or may be like      USA                       120
    120           40                                             Washington           40

    I have tried some aggregate functions like below:

    =SUMX(VALUES(Query[City]),CALCULATE(SUM(Query[AssetCount])))

    =CALCULATE(SUM(Query[AssetCount]),SUMMARIZE('Query',Query[City]))

    Here Query is table for Data Model & City can be replaced by Country.
    but not working.
    So showing such counts on same row is possible or not?




    Thursday, January 02, 2014 11:23 AM
  • Did you try with ALL function?

    http://technet.microsoft.com/en-us/library/ee634802.aspx

    so expression would be like

    =SUMX(ALL(TableName[City]), Query[AssetCount])


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, January 02, 2014 11:37 AM
  • ok, so you basically do not want so called Visual Totals - meaning that higher levels should only be aggregated by the visible descendants

    in order to accomplish this in DAX we need to check on which level we are (City or Country) and use different calculations:

    Value_City:=SUM('Query'[AssetCount])

    Value_Country:=CALCULATE(SUM('Query'[AssetCount]), ALLEXCEPT('Query', 'Query'[Country]))

    the tricky part comes when we try to find out, what is currently selected if you do your selection via a slicer
    usually you would use:

    Value:=IF(ISFILTERED('Query'[City]), [Value_City], [Value_Country])

    but the problem is that if you use your City-column as a slicer, ISFILTERED will always return true!
    to work around this issue you need two City-columns, one that is used in the hierarchy and one that is used as a slicer

    this is very similar to what Alberto describes here: http://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax

    hth,
    gerhard


    - www.pmOne.com -

    Thursday, January 02, 2014 1:16 PM