Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Kuri_191
Frequent Visitor

Summarize Table with two categories

‎11-21-2022 11:58 AM

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.

 

RatingYearWeight
AFY200.318069
BFY200.415842
CFY200.261139
AFY210.452256
BFY210.351522
CFY210.182581
AFY220.671916
BFY220.228346
CFY220.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.

 

Kuri_191_0-1669060451641.png

This is the formula that I'm currently using, with no success so far:

 

Test_Table = SUMMARIZE('Actual Input',
'Actual Input'[Rating],
'Date'[Year],
"Weight",
Divide(Calculate(COUNT('Actual Input'[concat1_ (period/rate)])),
CALCULATE(COUNT('Actual Input'[concat1_ (period/rate)]),ALL('Actual Input'),ALL('Date'[Year]))))
Message 1 of 5
425 Views
0
1 ACCEPTED SOLUTION
ERD
Community Champion
Community Champion

‎11-23-2022 08:33 AM

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] ) )
        )
)

 

ERD_0-1669221223808.png

 

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!

View solution in original post

Message 4 of 5
313 Views
4 REPLIES 4
ERD
Community Champion
Community Champion

‎11-23-2022 08:33 AM

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] ) )
        )
)

 

ERD_0-1669221223808.png

 

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!

Message 4 of 5
314 Views
Kuri_191
Frequent Visitor
In response to ERD

‎11-23-2022 11:43 AM

It worked! Thanks!

Message 5 of 5
306 Views
0
FreemanZ
Super User
Super User

‎11-21-2022 06:22 PM

how does your dataset look like?

Message 2 of 5
358 Views
0
Kuri_191
Frequent Visitor
In response to FreemanZ

‎11-23-2022 05:06 AM

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)RatingYearID
01/01/2020AA20206171
01/01/2020AA20206188
01/01/2020AA20206279
01/01/2020AA20206332
01/01/2020AA20206338
01/01/2020AA20206398
01/01/2020BB20206194
01/01/2020BB20206195
01/01/2020BB20206214
01/01/2020BB20206222
01/01/2020BB20206224
01/01/2020BB20206234
01/01/2020BB20206237
01/01/2020BB20206242
01/01/2020BB20206260
01/01/2020BB20206261
01/01/2020BB20206267
01/01/2020BB20206273
01/01/2020CC20206178
01/01/2020CC20206183
01/01/2021AA202113827
01/01/2021AA202113847
01/01/2021AA202113848
01/01/2021AA202113891
01/01/2021BB202113966
01/01/2021BB202113989
01/01/2021CC202113833
01/01/2021CC202113909
01/01/2021CC202113921
01/01/2021CC202114042
01/01/2021CC202114135
01/01/2021CC202114159
01/01/2021CC202114169
01/01/2021CC202114195
01/01/2021CC202114224
01/01/2021CC202114288
Message 3 of 5
326 Views
0

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

View All