cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dcasiala
Regular Visitor

Calculating a value from a multi column table based on multiple selections

I have a table loaded in Power BI.  It has list of about 50 rows with two columns forming a category (7) and a sub-category (approx 7 per) hierarchy followed by 9 columns of characteristics with values based on the second indexed column.

 

I want to calculate a value as a sum of the values in the column based on a multi-select (one each in the sub-category for example).

 

Ideally I want to use different slicers (based on each Category) to select the sub-category and then have all NINE column values be the sum of the selections.  If I don't select a sub-category, I would like it's value to be left off the sum.

 

If the table looks like this...

 

Cat     Sub Cat     Character 1      Character 2  ...   Character 9

A        X                      0                     2                         1

A        Y                      1                     1                         2

A        Z                      0                     0                         1

B        M                     1                     0                         0

B        N                     2                     1                         0

B        P                      0                    2                          1

...

G       F                      1                    1                           0

 

I would like to have a slicer pick X from a list of A's, a slicer pick N from a list of B's (etc).  I need the sum of each characteristic to be able to be presented in a visual with the 9 characteristics .  In my example, a selection of X and N would yield

 

Char 1 = 2

Char 2 = 3

...

Char 9 = 1

 

Thus I would rank order the characteristics Char 2, Char1, Char 9 in the central visualization.

 

I don't know how to create the custom calculation that would work on this multi-select.  Advice welcome.

1 ACCEPTED SOLUTION

Hi @dcasiala,

Congratulations, you find a solution at present. Please mark it as answer, so more peoplwe will get useful information from here.

Thanks,
Angelia

View solution in original post

7 REPLIES 7
Anonymous
Not applicable

Please help,

 

I want Calculated Row for No of Shop Drawings with Rev 0, 1 , 2 , 3 submitted by Subcontractor, The formula I used is working fine in while creating measure but its not working when I am creating calculated column

 

Calculated Column = CALCULATE (COUNT('Shop Drawings'[Subcontractor]),'Shop Drawings'[Subcontractor]="ACCESS 1", 'Shop Drawings'[Rev]="0")

 

 

Subcontractor

Revo 0

Rev 1

Rev 2

Rev 3

ACCESS 1

2

3

1

0

dcasiala
Regular Visitor

I have a table loaded in Power BI.  It has list of about 50 rows with two columns forming a category (7) and a sub-category (approx 7 per) hierarchy followed by 9 columns of characteristics with values based on the second indexed column.

 

I want to calculate a value as a sum of the values in the column based on a multi-select (one each in the sub-category for example).

 

Ideally I want to use different slicers (based on each Category) to select the sub-category and then have all NINE column values be the sum of the selections.  If I don't select a sub-category, I would like it's value to be left off the sum.

 

If the table looks like this...

 

Cat     Sub Cat     Character 1      Character 2  ...   Character 9

A        X                      0                     2                         1

A        Y                      1                     1                         2

A        Z                      0                     0                         1

B        M                     1                     0                         0

B        N                     2                     1                         0

B        P                      0                    2                          1

...

G       F                      1                    1                           0

 

I would like to have a slicer pick X from a list of A's, a slicer pick N from a list of B's (etc).  I need the sum of each characteristic to be able to be presented in a visual with the 9 characteristics .  In my example, a selection of X and N would yield

 

Char 1 = 2

Char 2 = 3

...

Char 9 = 1

 

Thus I would rank order the characteristics Char 2, Char1, Char 9 in the central visualization.

 

I don't know how to create the custom calculation that would work on this multi-select.  Advice welcome.

Hi @dcasiala,

For your requirement, please create a slicer including [sub cat] field, create measure using the similar formulas.

Char1 = CALCULATE(SUM(Test[Charecter1]),ALLSELECTED(Test))

Char2 = CALCULATE(SUM(Test[Charecter2]),ALLSELECTED(Test))

Char9 = CALCULATE(SUM(Test[Charecter9]),ALLSELECTED(Test))


Then you can create a Multi-row-card, add the measures in it, please review the following screenshot.

1.PNG

But we can't order the measures' value to date.

Best Regards,
Angelia

Thanks.  The calculation syntax is good but I cannot get to my intention with the multi-row card.  I would like to use chiclet or the list slicer to select the different sub categories and i was hoping to use the custom visual enlighten stack shuffle to display the top score of the calculated fields.

 

In the end, I am hoping to enable IF A=x and B=y... G=z, then the score for Char1 = n, Char2 = m,... Char3 =p and based on those selections, the TOP CHARs are (example) 2, followed by 3 followed by 1.

 

The enlighten stack shuffle allows me to order the top scores of a simple table easily enough.  But I need it to pull in the values of the calculations as they are created by the other slicers. 

 

Do you know if this is possible?

Hi @dcasiala,

As I tested, we can add multiple measures in the order by field, but the category will order by the sum of multiple measure, and we can not add a measure in category field. So it's impossible to get your expected result.

Best Regards,
Angelia

Your help has been invaluable.  Thank you.  I have mostly what I want but wish I could find a way to make it more elegant.  My solution at present uses the slicer to pick (multiples) from the list of Cat/Sub Cat list.  The scores for each of the 9 dimensions accurately sums only the rows selected and each of the 9 is displayed in a KPI visualization.

 

By visually inspecting the nine numbers I can select the dimension from a (separate) slicer (chiclet), I can filter a list of choices that are aligned to the dimension (which was my original intention)..

 

I appreciate your help with the DAX code and advice on on the visuals and their limits.

Hi @dcasiala,

Congratulations, you find a solution at present. Please mark it as answer, so more peoplwe will get useful information from here.

Thanks,
Angelia

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors