Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I'm hoping to use PowerBI to show overall market trends as well as trends in individual accounts. My data has several different slicers, e.g. location, device type, etc.
Is is possible for me to show two lines on a line graph, one for the overall marketplace average, and one for the individual account selected?
Solved! Go to Solution.
Hi @swong,
Is is possible for me to show two lines on a line graph, one for the overall marketplace average, and one for the individual account selected?
Using ALL/ALLEXCEPT could achieve it as @Anonymous has mentioned above. The formulas below are for your reference.
overall marketplace average = CALCULATE ( AVERAGE ( 'RevenueTable'[Revenue] ), ALLEXCEPT ( RevenueTable, 'RevenueTable'[Column_Name_that_you_used_for_Axis] ) )
individual account selected = SUM ( 'RevenueTable'[Revenue] )
If you still cannot figure out the measure to do it, you can just post your table structures with some sample/mock data and your expected result. So that we can better assist on this issue.
Regards
I think I'm understanding a little more, but I'm still confused about the Column_Name_that_you_used_for_Axis
This is my example data. When I used the formulas you suggested (I changed the individual SUM to an AVERAGE because I would like to compare an individual average gainst the marketplace overall), I got this graph:
I was hoping for th black line to look more like this teal one below, which is the average of the overal marketplace broken down by day.
The [Column Name that you used for Axis] is the column that you have placed into the X-Axis area of your graph. As you have noted, you are getting a graph that is a straight line. The ALL method ignores all context, thus it doesn't matter where on the graph it is placed, it always gets the same answer.
ALLEXCEPT however, ignores all context except for the context provided in the columns you specify. So by placing your X-Axis column into ALLEXCEPT, it will take that context into account.
Oh I see. I understand that definition, and now my formula is
Overall = CALCULATE(AVERAGE(Sheet1[Revenue]), ALLEXCEPT(Sheet1, Sheet1[Date]))
But this still gives me the same graph with the straight black line.
For reference, my individual formula is:
Individual = AVERAGE(Sheet1[Revenue])
Hi @swong,
What columns are you using as Slicers on the report? Are these columns in another table? If that is the case could you try using ALL function with these columns in the measure like below to see if it works?
Overall = CALCULATE ( AVERAGE ( Sheet1[Revenue] ), ALL ( Sheet2[Location] ), ALL ( Sheet3[Device Type] ) )
Regards
I used to have slicers, but I took them off for simplicity/to figure this first issue out, but those columns weren't being used anywhere else.
Here's my dashboard: https://msit.powerbi.com/view?r=eyJrIjoiNzA2MDdiODQtZTNhNC00NTNkLWExM2MtNGJhMGY2YWU1ZTRiIiwidCI6Ijcy...
Why would the slicers affect the display?
Hi @swong,
A little weird! So could you share a sample pbix file(with just some sample/mock data) which can reproduce this issue? So that we can help further investigate on it. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Regards
Thank you so much! Here's the OneDrive link: https://microsoft-my.sharepoint.com/personal/t-sewong_microsoft_com/_layouts/15/guestaccess.aspx?gue...
Let me know if the permissions don't work.
I have a follow-up question -- would it be possible to set the "Overall Line" to be the average revenue for a single color, and for the "Individual" line to be the average line for single account within that color?
More broadly, is it possible to have two lines on the same line graph affected by different slicers?
Hi @swong,
I have a follow-up question -- would it be possible to set the "Overall Line" to be the average revenue for a single color, and for the "Individual" line to be the average line for single account within that color?
More broadly, is it possible to have two lines on the same line graph affected by different slicers?
Yes, the formula below should work in this scenario.
Overall = CALCULATE(AVERAGE(Sheet1[Revenue]), ALLEXCEPT(Sheet1, Sheet1[Date],Sheet1[Color]))
Regards
Hello again!
Whe I add date as a slicer for my table, it works fine.
However, I'd like to have two line graphs both affected by the same date slicer. I thought to do this by creating and aux table, where each distinct date has a distinct value, the creating a relationship between Date1 of Table1 and the aux table, and a relationship between Date2 of Table2 and the aux table.
The slicer based on the aux table's Date column affects both individual lines on my two line graphs, but not the overall line. Is there a way to make this slicer affect all lines?
Oh I see! Thank you so so much for all of your help! 🙂
You could but you'll need to get tricky. I can think of a few ways to achieve this, so i'll offer one method and if that isn't suitable i can suggest other ideas.
You'll need a single measure that shows everything on the market, using an ALL or ALLEXCEPT depending on what filters you want to constrain this with (i.e. maybe a date range).
Next, you'll need a measure that does the same as the previous, just without the ALL/ALLEXCEPT. Now, put both those measures onto your line graph.
One issue you might face however, is that doing this you are going to have a shared Y-Axis. This means unless you are using a function that normalizes data in some way, you might not be able to get much value out of the smaller line. For example "SUM" data will be more problematic compared to data such as % growth.
Thanks for the response!
I'm still fairly new to PowerBI, so I'm not sure what you mean.
I would create a new measure showing my average revenue for everything? How would I include the AL or ALLEXCEPT? Woud I be able to affect both measures with different filters?
I tried making some new
measure = ALL('RevenueTable'[Revenue])
but the measure can't be loaded: A table of multiple values was supplied where a single value was expected.
Try:
AllMeasure = CALCULATE( SUM('RevenueTable'[Revenue]), ALL('RevenueTable') ) SomeMeasure = SUM('RevenueTable'[Revenue])
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |