"Dynamic" operation

[Anonymous]

Hello Everyone!

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...).

Any ideas of how to do this? 

Thank you!

[sebastiangestia]

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

[amitchandak]

@Anonymous , not very clear. Can you explain with an example.

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-pattern
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/500115

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]

@amitchandak 

I have on one side, tables with construction projects that look something like this:

Construction Site 01

Categorie        Type               Cost

Material           Painting         12

Machinery        Drill                23

Material           Concrete         15

Material            Bricks              17

Machinery        Crane              36

Personnel         Painter            14

Machinery        Excavator         18

On the other hand, I have a table with characteristics for all construction sites, like this:

Construction Site      Constructed Volume     Area of building      

01                                 600                                   210

02                                  380                                  700

03                                  510                                   265

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

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

[Anonymous]

@Anonymous 

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?

Regards
Paul

[Anonymous]

@Anonymous Well, I think is an option, the problem is that I might have too many categories, more than 10 in some cases. That´s why I wanted to do it kind of in an "automatic" way. But I´ll try this way. Thank you!

[harshnathani]

Hi @Anonymous ,

Try What if Parameters

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

Regards,

Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)