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

[dcasiala]

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.

[Anonymous]

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]

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.

[v-huizhn-msft]

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.



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

Best Regards,
Angelia

[dcasiala]

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?

[v-huizhn-msft]

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

[dcasiala]

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.

[v-huizhn-msft · 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