cancel
Showing results for
Did you mean: Frequent Visitor

## Dynamic calculation based on slicer selection

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  Super User III

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  Frequent Visitor

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!  Community Support

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:  (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. Frequent Visitor

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 #### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022. #### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories. #### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals. Top Solution Authors
Top Kudoed Authors
Users online (2,406)