"Dynamic" operation

Question

Hello Everyone!&nbsp;I have various excel documents with costs for construction projects by categories and I need to get some ratios, like costs/building area, but I need to be able to change the numerator using filters, and this way be able to get different ratios (total costs/building area, sum of cost of construction materials/building area, sum of cost of machinery/building area...).&nbsp;Any ideas of how to do this?&nbsp;&nbsp;Thank you!

amitchandak · Answer

Anonymous , not very clear. Can you explain with an example. 
&nbsp;
Either approach in the measure of these
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slicer-parameter-table-patternhttps://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115
&nbsp;
or isfiltered or isinscope should help
https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
&nbsp;
Try What if Parameters
&nbsp;
https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-what-if
https://www.youtube.com/watch?v=6a_5uderwAg
https://www.youtube.com/watch?v=7IEYHMvqZHY
&nbsp;
Regards,
Harsh NathaniDid I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

anonymous · Answer

amitchandak&nbsp;I have on one side, tables with construction projects that look something like this:Construction Site 01Categorie &nbsp; &nbsp; &nbsp;&nbsp; Type &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CostMaterial &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Painting &nbsp; &nbsp; &nbsp; &nbsp; 12Machinery &nbsp; &nbsp; &nbsp;&nbsp; Drill &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 23Material &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Concrete &nbsp; &nbsp; &nbsp; &nbsp; 15Material &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; Bricks &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 17Machinery &nbsp; &nbsp; &nbsp;&nbsp; Crane &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 36Personnel &nbsp; &nbsp; &nbsp; &nbsp; Painter &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 14Machinery &nbsp; &nbsp; &nbsp;&nbsp; Excavator &nbsp; &nbsp; &nbsp; &nbsp; 18&nbsp;On the other hand, I have a table with characteristics for all construction sites, like this:&nbsp;Construction Site &nbsp; &nbsp;&nbsp; Constructed Volume &nbsp; &nbsp; Area of building &nbsp; &nbsp; &nbsp;01 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 600 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 21002 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 380 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 70003 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; 510 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 265&nbsp;And I need to present the costs/area or by volume, and I need to be able to show with a filter the total cost, or just the material costs, and so on... like this&nbsp;Construction site 01:Total cost/area = 135/210, but then be able to say, I want to know now what are the Material costs/area = 44/210, by applying a filter to the numerator&nbsp;&nbsp;&nbsp;

sebastiangestia · Answer

Hello @mepj

To get started, if all Excel files have the same number of columns, you should use the "folder" connector and with this you can import and integrate all the files into a single operation.

From there, you can transform your model and perform all the operations you mention.

Best regards!

Did I answer your question? Mark my message as a solution. I'll appreciate the Kudos!!

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

anonymous · Answer

Anonymous&nbsp;What I can think of is create measures for each ratios, then create a slicer to filter which measures to display in the visual. It is doable, but Is this what you want?&nbsp;RegardsPaul

Forum Discussion

"Dynamic" operation

6 Replies

Related content

Dynamic Subscription failing "PaginatedReportUnknownError"

Dynamic format seems to add additional "-" for negative values

Dynamic Conversion rates in USD based on the "Order Booked date" or "Date"

Dynamic "latest date" filter for automated email report returns 2 dates instead of 1 after refresh

Dynamic "Pivot" Table