cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Enileda3
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?

 

 Ratio1Ratio2Ratio3Ratio4Cost
Company A0.50.20.30.13000
CompanyB0.10.10.10.32000

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Enileda3
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!

Ailsa-msft
Community Support
Community Support

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:

Ailsa-msft_0-1622107554882.png

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

Ailsa-msft_1-1622107554885.png

(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 :

Ailsa-msft_2-1622107554887.png

Ailsa-msft_3-1622107554888.png

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.

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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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