cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
SANGST
Helper I
Helper I

Matrix Graph equivalent

I have a table on input data formatted as per below (left of image).

I'm trying to plot the trend in value for each catergory overtime as a percentage of each days total (graph, right of image).

 

In excel i would first create a pivot table (middle image), with date's as rows, and the catergory pivoted to create 4 columns, and then values summed and displayed as % of row total. That would then automatically produce the desired graph as a pivot chart.

 

I can create a desired table in powerbi via the matrix table - but i'm then unable to then create the corresponding graph. Can anyone advise how i can create a graph in that format in powerbi?

 

example.PNG

 

 

1 ACCEPTED SOLUTION
MFelix
Super User III
Super User III

Hi @SANGST 

 

Create the follwing measure:

% of total by date = SUM('Table'[Value]) / CALCULATE(SUM('Table'[Value]), ALLSELECTED('Table'[Category]))

 

Now create the line chart with the following setup:

  •  Axis: Date (Categorical)
  • Legend: Category
  • Values [%of total by date]

Check PBIX file attach.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User III
Super User III

Hi @SANGST 

 

Create the follwing measure:

% of total by date = SUM('Table'[Value]) / CALCULATE(SUM('Table'[Value]), ALLSELECTED('Table'[Category]))

 

Now create the line chart with the following setup:

  •  Axis: Date (Categorical)
  • Legend: Category
  • Values [%of total by date]

Check PBIX file attach.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

Hi Felix - that answer worked perfectly, thank you so much! Also, much appreciate the quick response

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

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.