cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Calculated columns as axis & value

I have a bunch of calculated dax columns that I want to show as a visual. If I use a normal bar chart I get the following image where because I do not have any fields in the axis field. The titles of each of the calculated columns are what I want the x-axis to be similar to how it is in the funnel chart below. 

 

Barchart.JPG

The funnel chart only requires the value field to be filled in and it creates the following image which is kind of what I want but it needs to be vertical similar to the last image. 

 

Barchart2.JPG

 

This final image is what I want to achieve with my calculated columns but so far I have had no luck in figuring this out. This visual was created using a different file which is irrelevant to the project I am working on now.  I believe that if I could unpivot the calculated columns then it would create the graph I am looking for but I can't figure out how to unpivot columns that are created in dax. Is there a way to unpivot dax columns or a visual on the marketplace to accomplish what I am trying to do? Or would I need to create my own custom visual to accomplish this?  Other ideas/thoughts?

 

Barchart3.JPG

 

 

Sample data file

 

1 ACCEPTED SOLUTION
Frequent Visitor

I ended up coming up with a solution for this. It doesn't maintain relationships but it works. 

 

Totals Table = 
UNION(
    SUMMARIZE(Sheet4,"Cost",SUM(Sheet4[January 2020]),"Month","January 2020"),
    SUMMARIZE(Sheet4, "Cost", SUM(Sheet4[February 2020]), "Month", "February 2020"),
    SUMMARIZE(Sheet4,"Cost",SUM(Sheet4[March 2020]),"Month","March 2020"),
    SUMMARIZE(Sheet4, "Cost", SUM(Sheet4[April 2020]), "Month", "April 2020"),
    SUMMARIZE(Sheet4,"Cost",SUM(Sheet4[May 2020]),"Month","May 2020"),
    SUMMARIZE(Sheet4, "Cost", SUM(Sheet4[June 2020]), "Month", "June 2020"),
    SUMMARIZE(Sheet4,"Cost",SUM(Sheet4[July 2020]),"Month","July 2020"),
    SUMMARIZE(Sheet4, "Cost", SUM(Sheet4[August 2020]), "Month", "August 2020"),
    SUMMARIZE(Sheet4,"Cost",SUM(Sheet4[September 2020]),"Month","September 2020"),
    SUMMARIZE(Sheet4, "Cost", SUM(Sheet4[October 2020]), "Month", "October 2020"),
    SUMMARIZE(Sheet4,"Cost",SUM(Sheet4[November 2020]),"Month","November 2020"),
    SUMMARIZE(Sheet4, "Cost", SUM(Sheet4[December 2020]), "Month", "December 2020"))

View solution in original post

8 REPLIES 8
Frequent Visitor

I ended up coming up with a solution for this. It doesn't maintain relationships but it works. 

 

Totals Table = 
UNION(
    SUMMARIZE(Sheet4,"Cost",SUM(Sheet4[January 2020]),"Month","January 2020"),
    SUMMARIZE(Sheet4, "Cost", SUM(Sheet4[February 2020]), "Month", "February 2020"),
    SUMMARIZE(Sheet4,"Cost",SUM(Sheet4[March 2020]),"Month","March 2020"),
    SUMMARIZE(Sheet4, "Cost", SUM(Sheet4[April 2020]), "Month", "April 2020"),
    SUMMARIZE(Sheet4,"Cost",SUM(Sheet4[May 2020]),"Month","May 2020"),
    SUMMARIZE(Sheet4, "Cost", SUM(Sheet4[June 2020]), "Month", "June 2020"),
    SUMMARIZE(Sheet4,"Cost",SUM(Sheet4[July 2020]),"Month","July 2020"),
    SUMMARIZE(Sheet4, "Cost", SUM(Sheet4[August 2020]), "Month", "August 2020"),
    SUMMARIZE(Sheet4,"Cost",SUM(Sheet4[September 2020]),"Month","September 2020"),
    SUMMARIZE(Sheet4, "Cost", SUM(Sheet4[October 2020]), "Month", "October 2020"),
    SUMMARIZE(Sheet4,"Cost",SUM(Sheet4[November 2020]),"Month","November 2020"),
    SUMMARIZE(Sheet4, "Cost", SUM(Sheet4[December 2020]), "Month", "December 2020"))

View solution in original post

Community Support
Community Support

Hi @slauer12 , 

It seems that you want to unpivot table and create cumulative value in chart, right? If so, you could refer to my sample for details.

I unpivot columns and create a month column, the create a measure to cumulative value.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

Thanks for your reply @dax.

 

Yes that is what I am trying to accomplish except I have an ever growing list of projects and can't manually enter data to manipulate. I could be wrong but that's what it looks like what you did to take the data from the calculated columns to power query. As some of these projects have not yet started and are getting updated all the time it would be very tedious to update the static information as well as change the dollar amounts each time something gets updated. For a frame of reference, I have roughly 400 projects that I am trying to do this for and the list is growing weekly and to be able to keep up with it would be tough. 

Community Support
Community Support

Hi  @slauer12 , 

In  my sample, there is no calculated column in it, I use M code to create new column, so you don't need to insert data manually, you could copy the M code from Advanced Editor , and use this in your report.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

I copied and pasted your code with a few edits to call the columns I need and I get list errors. How does it know which source to pull from? Is this because the long file name isn't pointing to the correct sheet?

Frequent Visitor

Oh I see. I overlooked this. Do you know of a tutorial where I can learn how to do this? I don't completely understand what the source line means and how to replicate it for my use. I understand the rest of the code but not sure where the json document is and how to figure out the long line for file. 

Super User III
Super User III

@slauer12 share some data. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors