Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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êsThanks 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!
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |