topic Re: Not be grouped by in DAX Commands and Tips

Not be grouped by

Anonymous — Wed, 11 May 2022 14:06:18 GMT

Hey! I got a bar chart, x-axis is list of brands, y-axis measure. Looks like this:

(can't show x-acis due to company agreement). I need a measure that will be an average value of y-axis measure by x-axis category. I can calculate this measure:

Average sat =

AVERAGEX(

KEEPFILTERS(VALUES('Both'[Brand])),

CALCULATE([% satisfaction top 2]))
But when I put it as a line on barchart it don't shows me what I need, I need a straight line.

Re: Not be grouped by

Whitewater100 — Wed, 11 May 2022 16:20:38 GMT

Hi:

Can you try a Line & Clustered chart, using the overall measure as seconday Y? I'll paste image for more clarity. My example is on profit margin %, but can be done for your overall SAT measure. I hope this helps..

Measure for flat line:

Profit Margin% ALL =

var profit = CALCULATE(SUM(Data[Profit]),ALL(Data))

var sales = CALCULATE(SUM(Data[Sales]),ALL(Data))

return

DIVIDE(profit,sales)

Re: Not be grouped by

Anonymous — Thu, 12 May 2022 08:57:54 GMT

I won't work because let's say your sales in my case is a measure, not a column

Re: Not be grouped by

SpartaBI — Thu, 12 May 2022 10:42:33 GMT

Anonymous just add your measure as a constant line in the analytics tab of the visual formatting :

Showcase Report – Contoso By SpartaBI

Re: Not be grouped by

Whitewater100 — Thu, 12 May 2022 11:44:57 GMT

Hi:

Can you provide example data (and image of key tables in your data model)as it should be possible to create a measure showing the values you want as a straight line?

Thanks

Re: Not be grouped by

Anonymous — Fri, 13 May 2022 05:46:02 GMT

Table: Both;
Columns: Brand, Person_ID, Question, Answer, answer_code, Type
Measure:

% satisfaction =

var t1b = CALCULATE(distinctcount('Both'[person_id]), or(Both[answer_code] = "5", Both[answer_code] = "4"), Both[Type] = "Satisfaction")

var total_cnt = calculate(distinctCOUNT(Both[person_id]), Both[Type] = "Satisfaction")

return t1b/total_cnt

So, it just counts the % of positive answers, and I show it as % per brand in a bar chart(x-axis - brand, y-axis - satisfaction measure. How to show average satisfaction by brand on the same bar chart as line?

Re: Not be grouped by

Anonymous — Fri, 13 May 2022 06:14:06 GMT

The answer is correct, very cool. But I should use not constant line, but average line, thanks, I didn't know about these feature.