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
- Dynamic calculation based on slicer selection

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

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

Dynamic calculation based on slicer selection

05-25-2021
01:47 AM

Hi,

I am trying to calculated a simple sum of columns of Ratio based on slicer selection which will then be used to multiply the cost. The challenge comes as there are 50 ratios columns and the tables is 40 million rows. I tried searching for solutions and the solution is to unpivot the columns. However, doing that creates billions of rows which using Power BI Service is taking too long to create the table. Is there any way this could be done?

Ratio1 | Ratio2 | Ratio3 | Ratio4 | Cost | |

Company A | 0.5 | 0.2 | 0.3 | 0.1 | 3000 |

CompanyB | 0.1 | 0.1 | 0.1 | 0.3 | 2000 |

So users will have a filter to select any ratio that they want e.g. Ratio1 , Ratio3, Ratio4 and the calculation for Company A will be Total Ratio = 0.5+0.3+0.1 = 0.9. This ratio will be used to calculation Final Cost = 0.9 * 3000 = 2700

Suggestions are welcome! Thanks!

4 REPLIES 4

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

05-27-2021
03:00 AM

Hi @Enileda3 ,

Has you mention believe that the best option is to unpivot your data, don't know what is the format of your input but taking into account the size of the information this should be done on the server side and not on Power BI.

However believe this can be achieved using a disconnected table and some measures.

Disconneted table:

Ratio

Ratio1 |

Ratio2 |

Ratio3 |

Ratio4 |

Measures:

```
ratio =
IF (
"Ratio1" IN VALUES ( Ratios[Ratio] ),
CALCULATE ( SUM ( 'Ratio/cost'[Ratio1] ) )
)
+ IF (
"Ratio2" IN VALUES ( Ratios[Ratio] ),
CALCULATE ( SUM ( 'Ratio/cost'[Ratio2] ) )
)
+ IF (
"Ratio3" IN VALUES ( Ratios[Ratio] ),
CALCULATE ( SUM ( 'Ratio/cost'[Ratio3] ) )
)
+ IF (
"Ratio4" IN VALUES ( Ratios[Ratio] ),
CALCULATE ( SUM ( 'Ratio/cost'[Ratio4] ) )
)
```

```
ratio_ =
var temp_table = UNION(
SELECTCOLUMNS('Ratio/cost', "Ratio", 'Ratio/cost'[Ratio1], "Cat", "Ratio1"),
SELECTCOLUMNS('Ratio/cost', "Ratio", 'Ratio/cost'[Ratio2], "Cat", "Ratio2"),
SELECTCOLUMNS('Ratio/cost', "Ratio", 'Ratio/cost'[Ratio3], "Cat", "Ratio3"),
SELECTCOLUMNS('Ratio/cost', "Ratio", 'Ratio/cost'[Ratio4], "Cat", "Ratio4")
)
var Result = sumx(FILTER(temp_table, [Cat] in VALUES(Ratios[Ratio])), [Ratio])
Return
Result
```

On the previous measure you need to had a row per each of your columns.

Don't know in terms of performace what is the result of each of this ones but you need to try it out.

Then use this measure to multiply by the sum of the costs.

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-30-2021
09:14 AM

Thanks for the solution! I have also done very similar way using measures and a disconnected table.. the performance is really bad... I will try out using your formulas to see if the performance improve. Thank you!

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

05-27-2021
02:27 AM

Hi @Enileda3

If you want to use columns as a slicer to filter data , you have to unpivot the columns , then you can only get the column by Ratio .

(1)Select the columns you want to use in slicer and click “Unpivot Only Selected Columns” , and then you will get a column like this:

(2)Return to desktop view ,and add a slicer with field “Ratio” .

(3)Create a measure to calculate the product for 'Table'[Value] and 'Table'[Cost] .

Measure = SUM('Table'[Value])*SELECTEDVALUE('Table'[Cost])

(4)Add a table visual and put 'Table'[Company] and measure in it .The final effective is as shown :

You can see the result in the table visual without too many rows of data .

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

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

05-30-2021
09:13 AM

As mentioned, unpivoting the columns to rows will results in billions of rows.. Nevertheless, I have tried running it in Power BI Service, it ran for almost 10 hours and fail.

Announcements

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

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Featured Topics

Top Solution Authors

User | Count |
---|---|

244 | |

141 | |

88 | |

64 | |

52 |

Top Kudoed Authors

User | Count |
---|---|

258 | |

173 | |

99 | |

78 | |

76 |