Cumulative formula for multiple columns

[boyddt_mn]

The company that I work for has a requirement for Control Charts and Pareto charts for each of the metrics that we report on. Our current process is to manually create each measures which is extensive. What I would like to do is to use something like Bravo, if not Bravo, to create the code within JSON files that would pre-create the measures. For some context we are looking to move over 80 metrics from Excel to Power BI and I am not looking forward to creating all of these measure by hand. Any suggestions would be appreciated.

[AlexisOlson · Accepted solution]

There may be other options, but I know you can do this with Tabular Editor scripting:

Useful script snippets | Tabular Editor Documentation

If you're like me and not fluent in C#, try using ChatGPT or Claude to help adapt the scripts to exactly what you need.

[danextian · Accepted solution]

HI @boyddt_mn ,

Have you tried using the DAX query view? You should be able to write multiple measures into the model at once by using DEFINE and MEASURE keywords, the table the measures are to be saved to, the names and the formula.

[Anonymous]

Your solution is so great AlexisOlson and danextian 

Hi, @boyddt_mn 

Thank you so much for raising your questions in the community! If Super's reply solves your question, you can mark it as a "solution" so that other friends who are experiencing similar problems can also find the answer. This will go a long way in helping others in the community. Thank you again for participating!

Best Regards

Jianpeng Li

[sio2Graphs]

Hello,

If you can move away from creating all of the measures manually for the Pareto charts, you can use visual "Pareto+" to create a graph, table and legend, or you can use "Pareto by sio2Graphs" to produce the graph, no need for measures.

sio2Graphs 😀

sio2graphs on AppSource 

[boyddt_mn]

I appreciate the thought but my company isn't keen on using third part visuals unless absolutely necessary. Their concern, warranted or not, is that if we build our eco-system on third party visual and then they (the develolpers of the visual) stop supporting it, want to move to a pay to play model or anything else that can disrupt our process, can cause us to miss contractually required reporting.

[danextian · Accepted solution]

HI @boyddt_mn ,

Have you tried using the DAX query view? You should be able to write multiple measures into the model at once by using DEFINE and MEASURE keywords, the table the measures are to be saved to, the names and the formula.

[boyddt_mn]

I will look at DAX Studio and see if I can sort it out. I'll let you know what I find out.

[danextian · Accepted solution]

Late reply but that is DAX Query view native  in Power BI Desktop.

[boyddt_mn]

I've started looking at the DAX query and it seems as though it will work, but it also seems as though there is a huge learning curve. Good thing I have no plans for the weekend.

[danextian]

In DAX query view, you can  right click a measure, click quick queries and then hit "define all measures in this model" or the other two in my screenshot and that will give you an idea of what the syntax is. Non-existing functions or references will be errored out. Once all measures have been set (no errors, etc), delete everything from EVALUATE and then click update with model changes.

[AlexisOlson · Accepted solution]

There may be other options, but I know you can do this with Tabular Editor scripting:

Useful script snippets | Tabular Editor Documentation

If you're like me and not fluent in C#, try using ChatGPT or Claude to help adapt the scripts to exactly what you need.

[boyddt_mn]

Thank you. I will take a look at Tabular Editor and let you know