Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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
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 |
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.
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |