Dynamic calculation based on slicer selection

[Anonymous]

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!

[MFelix]

Hi @Anonymous ,

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.

[Anonymous]

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!

[Anonymous]

Hi @Anonymous 

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.

[Anonymous]

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.