Forum Discussion
Summarize Table with two categories
Hello community!
Imagine that you have a table with a list of products and each product is given a specific rating (A,B, and C). These products are sold every year (2020 to 2022) and I want to see what is the weight of each product per year.
| Rating | Year | Weight |
| A | FY20 | 0.318069 |
| B | FY20 | 0.415842 |
| C | FY20 | 0.261139 |
| A | FY21 | 0.452256 |
| B | FY21 | 0.351522 |
| C | FY21 | 0.182581 |
| A | FY22 | 0.671916 |
| B | FY22 | 0.228346 |
| C | FY22 | 0.099738 |
As you can see, if you sum all the values for a specific year, they all add up to 1 (100%).
When I try to recreate this table on PowerBI, I'm struggling with the values, because it's not considering the value per year, but the value for all the years. Please see the screenshot below.
This is the formula that I'm currently using, with no success so far:
Hey Kuri_191 ,
Taking your data sample below + DAX to create a table:
Table_3_v2 = ADDCOLUMNS ( SUMMARIZE ( Table_3, Table_3[Rating], 'Date'[Year] ), "Weight", DIVIDE ( CALCULATE ( COUNT ( Table_3[concat1_ (period/rate)] ) ), CALCULATE ( COUNT ( Table_3[concat1_ (period/rate)] ), ALL ( Table_3[Rating] ) ) ) )
4 Replies
- ERD
Community Champion
Hey Kuri_191 ,
Taking your data sample below + DAX to create a table:
Table_3_v2 = ADDCOLUMNS ( SUMMARIZE ( Table_3, Table_3[Rating], 'Date'[Year] ), "Weight", DIVIDE ( CALCULATE ( COUNT ( Table_3[concat1_ (period/rate)] ) ), CALCULATE ( COUNT ( Table_3[concat1_ (period/rate)] ), ALL ( Table_3[Rating] ) ) ) )It worked! Thanks!
FreemanZSuper User
how does your dataset look like?
Hello Freemanz,
Thanks for the reply.
Here a small portion of the dataset. From all the 4 fields, only the Year is not from the same table. Year is on a dates table, but is connected with the table from the remaining 3 fields.
concat1_ (period/rate) Rating Year ID 01/01/2020A A 2020 6171 01/01/2020A A 2020 6188 01/01/2020A A 2020 6279 01/01/2020A A 2020 6332 01/01/2020A A 2020 6338 01/01/2020A A 2020 6398 01/01/2020B B 2020 6194 01/01/2020B B 2020 6195 01/01/2020B B 2020 6214 01/01/2020B B 2020 6222 01/01/2020B B 2020 6224 01/01/2020B B 2020 6234 01/01/2020B B 2020 6237 01/01/2020B B 2020 6242 01/01/2020B B 2020 6260 01/01/2020B B 2020 6261 01/01/2020B B 2020 6267 01/01/2020B B 2020 6273 01/01/2020C C 2020 6178 01/01/2020C C 2020 6183 01/01/2021A A 2021 13827 01/01/2021A A 2021 13847 01/01/2021A A 2021 13848 01/01/2021A A 2021 13891 01/01/2021B B 2021 13966 01/01/2021B B 2021 13989 01/01/2021C C 2021 13833 01/01/2021C C 2021 13909 01/01/2021C C 2021 13921 01/01/2021C C 2021 14042 01/01/2021C C 2021 14135 01/01/2021C C 2021 14159 01/01/2021C C 2021 14169 01/01/2021C C 2021 14195 01/01/2021C C 2021 14224 01/01/2021C C 2021 14288
