Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
swong
Helper II
Helper II

Average and individual account lines on line graph

Hi,

 

I have revenue data that I want to be able to slice in different ways -- e.g. by location, or device type. This data also includes individual accounts.

 

Is it possible to display the average of all accounts, as well as the performance for a single account? E.g. I want to display the overall average revenue for all accounts in Location A, and I also want to compare that to Account 1 on the same line graph.

 

 

1 ACCEPTED SOLUTION

@swong,

 

Hi Serena,

 

I found out the trick after re-research "All" and "Allexcept". Try this formula please. The fields of the slicers should com from table 'date' and table 'color'.

Overall =
CALCULATE (
    AVERAGE ( Sheet1[Revenue] ),
    ALLEXCEPT ( 'Date', 'Date'[Date] ),
    ALL ( Color )
)

Best Regards!

Dale

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

View solution in original post

15 REPLIES 15
v-jiascu-msft
Employee
Employee

Hi @swong,

 

Could you please tell me if your problem was resolved? Could you please share the answer or mark the proper answer as solution if it's convenient for you? That will be a help to the others.

Best Regards!
Dale

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

Hi @v-jiascu-msft,

 

I figured out how to display the two lines I want, but I'm having trouble slicing them the way I want to.

 

Right now, I have two graphs, both with an "Overall" line reflective of the entire marketplace, and an "Individividual" line that is sliceable, for example by color.

 

I also want to be able to slice all four lines by date, but my date slicer is only working on the Individual lines, not Overall lines.

 

unevenLines.PNG

 

The Overall lines are measures with the formula: Overall = CALCULATE(AVERAGE(Sheet1[Revenue]), ALLEXCEPT(Sheet1, Sheet1[Date]))

 

The Individual lines are measures with the formula: Individual = AVERAGE(Sheet1[Revenue])

 

I'm using the color slicer by creating an aux table and creating a relationship between it and each of my two tables (Sheet1 and Sheet2), but this didn't work for the date slicer. Is there a way to affect my Overall lines with the date slicer?

 

Here's the link to my dummy data pbix file:

https://microsoft-my.sharepoint.com/personal/t-sewong_microsoft_com/_layouts/15/SkySyncRedir.aspx?Ty...

Hi @swong,

 

Where is the field "Date" of the slicer from? You used a function "Allexcept" in the formula of "Overall", so "Overall" only can be filtered by Sheet1[Date].

I can't access the file from your link. Could you please share it with OneDrive or Dropbox etc. ?

 

Best Regards!

Dale

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

Hi Dale,

 

The "Date" slicer is coming from my "Date" aux table. This able allows my to use date as a slicer for both of my line graphs. I tried using

 

Overall = CALCULATE(AVERAGE(Sheet1[Revenue]), ALLEXCEPT('Date', 'Date'[Date]))

 

as my formula, but then the overall line is affected by all slicers.

 

Also, my .pbix file is called DateSlicerQ

 

Thanks!

Hi @swong,

 

I don't have access to your file. Maybe you gave me the link, not the share link. Could you please share it again?Average and individual account lines on line graph.JPG

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

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

Hi @swong,

 

The fields of the visualizations are from different table. That would be the trick. You already have a date table. So the axis of the line chart should be from the date table. And so does the slicer. Finally, only one date slicer is needed though there many dates in sheet1, sheet2 or more.

Average and individual account lines on line graph .jpg Average and individual account lines on line graph 2.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

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

Hi @v-jiascu-msft,

 

I'm still a little confused. If I change the axis on my graphs to the date from the date table, my overall line is no longer an overall line -- it is sliceable by color.

 

I'm looking for a way for my overall lines to be affected by the same date slicer (now from the date table), but not by color )from it's own color table).

 

 

Hi @swong,

 

According to my test, it didn't filtered by color. You can see it from the table visual. Please reference: https://1drv.ms/u/s!ArTqPk2pu-BkgQpsa9nmJlIOhVqQ.

 

Best Regards!

Dale

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

Hi @v-jiascu-msft,

 

I see -- yes, the color slicer does not affect the overall line, but the overall line doesn't change when I apply the date slicer. Is there a way to change it with the date slicer?

 

Thanks,
Serena

@swong,

 

Hi Serena,

 

I found out the trick after re-research "All" and "Allexcept". Try this formula please. The fields of the slicers should com from table 'date' and table 'color'.

Overall =
CALCULATE (
    AVERAGE ( Sheet1[Revenue] ),
    ALLEXCEPT ( 'Date', 'Date'[Date] ),
    ALL ( Color )
)

Best Regards!

Dale

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

Hi @v-jiascu-msft,

 

I see, that works for color!

 

Is it possible to make my graph affected by multiple slicers? e.g. color AND location. I tried adding my location table as an argument to ALL, but the ALL function doesn't take more than one argument.

Hi @v-jiascu-msft,

 

I played around a little more and figured out that for each slicer, I just need to have another ALL statement. Thank you so so much for your help!!

Hi @v-jiascu-msft, checking in about this question.

v-jiascu-msft
Employee
Employee

Hi @swong,

 

There are composite visualizations, such as "line and clustered column chart", "line and stacked column chart", which could display your demands. Could you please post a sample in text mode? Maybe we could create a demo.

 

Best Regards!

Dale

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.