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

Aggregation - New Table or Measure

First, I am a Power BI novice and please bear over with my scattered skills.

 

I have a Table with the following columns:

 

Week | Supplier | Store | Manufacturer | Sales

 

As there are many combinations of Supplier Chain, Store and Manufacturer, there are multiple rows per week.

 

1. How do I aggregate the weekly sales for each manufacturer?

 

2. The output is to be presented in a line chart. Should this be done in a new table or measure?

 

Thanks in advance for any help.

 

/depple

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
themistoklis New Contributor
New Contributor

Re: Aggregation - New Table or Measure

@depple

 

Create this new measure and add it on Values area in the graph:

 

% Share = 
VAR Volume =
    SUM('Table'[Sales])
VAR AllVolume =
    CALCULATE ( SUM('Table'[Sales]),ALL('Table'[Manufacturer]) )
RETURN
    DIVIDE ( Volume, AllVolume )
4 REPLIES 4
themistoklis New Contributor
New Contributor

Re: Aggregation - New Table or Measure

@depple

 

You actually dont need either of these 2.

 

Simply drag a new line chart to the page and put Week as Axis, Manufacturer as Legend and Sales as Values.

See image below.

 

 

Workspace also on this link

depple Regular Visitor
Regular Visitor

Re: Aggregation - New Table or Measure

themistoklis,

 

Thank you for your quick suggestion!

 

That was an easy solution. I am obviously not familiar with the capabilities the software.

 

And now, trying to display the manufacturer market share per week, I do however notice that "Show value as" => "Percentage of Total" (my translation from non-English version) is not showing up the way I would hope, but that is probably because is shows market share for all data points, instead of per week. Could you point me in the right direction for how to display per week market share? Would this be the time for a measure or new table?

 

Thanks in advance.

 

/depple

themistoklis New Contributor
New Contributor

Re: Aggregation - New Table or Measure

@depple

 

Create this new measure and add it on Values area in the graph:

 

% Share = 
VAR Volume =
    SUM('Table'[Sales])
VAR AllVolume =
    CALCULATE ( SUM('Table'[Sales]),ALL('Table'[Manufacturer]) )
RETURN
    DIVIDE ( Volume, AllVolume )
depple Regular Visitor
Regular Visitor

Re: Aggregation - New Table or Measure

themistoklis,

 

Worked perfectly! Thank you very much. I shall keep your formula within close reach for future challenges. :-)

 

/depple