Reduce tables in power bi to optimize the dashboard

[Ruchir]

Hi All, Need help with optimising of dashboard. Its a very simple dashboard with 19 factors where the user can select to choose the scoring of 19 factors on the scale of 1-10. Initially when i created scoring slicers I had to create 19 disconnected tables with just the scoring numbers of 1-10 to eliminate the dependency but now i want to reduce the number of data tables (probably consolidating 19 tables as 1 if possible due to some reasons). I did try to consolidate it with 19 columns with just scoring of each factor as rows and factor name as column in a table and turned the interactions off so that the slicers dont filter each other out. But I have come across a problem i need a card with the following measure calculation -selected value of one factor / total of all selected values of 19 factors. with the interactions off this calculation has gone for a toss and is not changing as i change slicers or displaying the correct value,

[Anonymous]

Hi @Ruchir ,

Please provide sample data in a usable form and show your expected results to better solve the problem for you.

Best regards,
Community Support Team_ Scott Chang

[Ruchir]

Hi, this is how the ideal output should look like, where user can provide a scoring for each factor. Sample data is simply one excel with Factor 01 to factor 10 and another is 10 independent tables with scoring 0 to 10 

[Anonymous]

Hi @Ruchir ,

I've made a few attempts, and here's what you can expect, but this only supports a single choice of factor:

Measure = var _select = SELECTEDVALUE(Dim[Factor])
RETURN IF(MAX('Factor name'[Factor])=_select,SUM('Dim'[Score]),0)

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Ruchir]

Hi Thank you for your time. Unfortunately I would have to allow users to select single value out of all the factors , independently and then calculate the weighted average. I did combine the fields as below and turned the interactions off. My problem right now is a blank measure created with the following dax code: 

Weighted avg = DIVIDE([selected value 1], [total], 0)

selected value 1 = SELECTEDVALUE('Table'[F1])

selected value 2 = SELECTEDVALUE('Table'[F2])

selected value 3 = SELECTEDVALUE('Table'[F3])

total= selected value 1 + selected value 2+ selected value 3

[Anonymous]

Hi @Ruchir ,

Please share the sample pbix file you are using.

Best regards,
Community Support Team_ Scott Chang

[Ruchir]

Hi Unable to upload pbix (maybe new member). But you can create the below table only and then put F1,F2,F3 as slicers, on clicking F1 i turned interactions off with F2 & F3 slicers, similarly with F2 turned off for F1 & F3 and for F3 turned off F1 & F2, now i can select any score for all 3 slicers (single select dropdowns). the weighted average measure which shows blank has all interactions on and the below calculation

Weighted avg = DIVIDE([selected value 1], [total], 0)

selected value 1 = SELECTEDVALUE('Table'[F1])

selected value 2 = SELECTEDVALUE('Table'[F2])

selected value 3 = SELECTEDVALUE('Table'[F3])

total= selected value 1 + selected value 2+ selected value 3

[Anonymous · Accepted solution]

Hi @Ruchir ,

It's not implemented exactly as your data model, you can view the data transformation via power query, but the final result should be what you expect. It's roughly divided into two slicers, one calculates the sum and the other calculates a specific weight percentage. You can check the results as follows:

specific Weight = var _s = SELECTEDVALUE('Table 2'[Attribute])
RETURN DIVIDE(CALCULATE(SUM('Table'[Value]),FILTER('Table',[Attribute]=_s)),[Total])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Ruchir]

Thank you so much it sorts out my problem