topic Re: Monthly Average as accessory line for other lines from legend in DAX Commands and Tips

Monthly Average as accessory line for other lines from legend

Damian88 — Sun, 19 Feb 2023 01:37:32 GMT

Hi,

I have a problem and i cant find solution enywere. I hope that someone already solved such case

I have a table with sales Values for different products and Shops (Sales - Product_name, Shop_ID, Value),

this table has relation with Shop list (Shop_ID, Territory_ID) and this table has relation with employee list (Territory_ID + Username)

I want to create a line chart by months with Market Share% for prodyct X with employee list and Market Share% for whole Team

Unfortynately there is no option to add more than one Field in Legend (wnen I add "Username" there is no option to show Value for whole team in the same Chart)

I dont want to create Measure for each Username because they will be changing. any Idea ?

Thank you!

Re: Monthly Average as accessory line for other lines from legend

FreemanZ — Sun, 19 Feb 2023 02:33:42 GMT

hi Damian88

could you depict your expected chart visually?

Re: Monthly Average as accessory line for other lines from legend

Damian88 — Sun, 19 Feb 2023 08:59:23 GMT

Hi,

Please Find bellow

Measure is :

Market Share % =
VAR _Selecte_Product = Sum(Value)
VAR _All_Products = Calculate(Sum(Value),Removefilters(Product_Name)
Return
Divide(_Selected_Product,_All,Products,0)

Re: Monthly Average as accessory line for other lines from legend

FreemanZ — Sun, 19 Feb 2023 10:19:31 GMT

hi Damian88

Try to plot two visuals with a measure like:

A Share % = VAR _avalue = CALCULATE( SUM (Table1[Value]), Table1[Product_Name] = "A" ) VAR _allvalue = CALCULATE( SUM(Table1[Value]), ALL(Table1[Product_Name]) ) Return Divide(_avalue, _allvalue, 0)

it worked like:

Indeed, Excel has better flexibility for your case.

If you don't have too many employee_id, you can write a measure for each.

If you have too many employee_id, then the linechart will be very crowed.

Re: Monthly Average as accessory line for other lines from legend

Damian88 — Sun, 19 Feb 2023 10:45:49 GMT

I Thnx for You answer, unfortunatelly it dont cover my case

I forgot to tell that there are few different Teams so employee_id will be dynamic based on a slicer (always arround 5/6 employee_id + Value for Team) so I cant create Measure for all of them.

I was more thinking, if there is option to 2 level Grouping - legend Fields option (i know that I can group Employees in and see on chart Value For Team but in this case legend wont show me singe emplyees)

Re: Monthly Average as accessory line for other lines from legend

Damian88 — Sun, 19 Feb 2023 10:48:51 GMT

and ofcourse it has to be on one chart 😕