Forum Discussion

WestWinter's avatar
WestWinter
Icon for Helper II rankHelper II
5 years ago
Solved

Summing up counts in a table

CountryStatus
AsiaRED
  JapanGREEN
  ChinaRED
  KoreaYELLOW

To

 

CountryCount of RedCount of YELLOWCount of GReen
Asia578
  Japan  1
  China1  
  Korea 1 

 

Hi all,

 

currently I am using a code that switches when a metric changes, so if the condition is at 1, it will be green, 2 yellow, red 3. (1st table)

Measure = switch(metrics[Metric 1 Condition], 1, "GREEN", 2, "YELLOW",3, "RED")
 
How do i add a count such that subtotals (like Asia) would sum up the count of all the GREEN/YELLOW/RED together instead?
Need Help
  • VahidDM's avatar
    VahidDM
    5 years ago

    Hi WestWinter 

     

    Use a Matrix in your report and add Status column to Columns and values [in Values set a aggregation to Count] and add Continent and Conuntry columns to the Rows, like this:

     

    Data I Used:

     

    Output:

    Did I answer your question? Mark my post as a solution!

    Appreciate your Kudos  !!

5 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    5 years ago

    Hi WestWinter 

     

    Use a Matrix in your report and add Status column to Columns and values [in Values set a aggregation to Count] and add Continent and Conuntry columns to the Rows, like this:

     

    Data I Used:

     

    Output:

    Did I answer your question? Mark my post as a solution!

    Appreciate your Kudos  !!

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi WestWinter ,

     

    I think the solution provided by VahidDM  is effective.

     

    Or you can try the resume dimension table and the fact table, the table relationship is connected according to the country, and the one-to-many relationship. Then construct a similar matrix visual table.


    If the problem is still not resolved, please point it out. Looking forward to your reply.


    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Jos_Woolley's avatar
    Jos_Woolley
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Hi,

    It's not clear from the data you've posted how your continents are stored? "Asia", for example, is not a country, though in the table you give it appears under that heading.

    I presume you have a simple dimension table which contains each Country together with its associated Continent?

    Regards

      • Jos_Woolley's avatar
        Jos_Woolley
        Icon for Solution Sage rankSolution Sage
        5 years ago

        So your second table is your expected results? So you want 3 separate measures - Count of Red, Count of Yellow, Count of Green - which at the Country level provide simple counts but at the Continent level provide different results? Can you explain how you arrive at a result of 5 for Count of Red|Asia?

        I think a much clearer example dataset together with expected results would be beneficial.

        Regards