- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Calculating a value from a multi column table base...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

dcasiala

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-08-2018
08:38 AM

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.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

v-huizhn-msft

Super Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-11-2018
06:05 PM

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

7 REPLIES 7

dcasiala

Frequent Visitor

Dynamic calculate Measure value based on Slicer Selection

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-08-2018
08:45 AM

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

Super Contributor

Re: Dynamic calculate Measure value based on Slicer Selection

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-08-2018
10:25 PM

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

Frequent Visitor

Re: Dynamic calculate Measure value based on Slicer Selection

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-09-2018
06:10 AM

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

Super Contributor

Re: Dynamic calculate Measure value based on Slicer Selection

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-09-2018
11:02 PM

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

Frequent Visitor

Re: Dynamic calculate Measure value based on Slicer Selection

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-10-2018
12:03 PM

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

Super Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-11-2018
06:05 PM

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

Thanks,

Angelia

ayahmed

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-28-2019
03:55 AM

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 |