Problems with AVG line on chart

[baneworth]

Following Issue:

I am collecting issues found on a product. X axis shows the product ID and the y axis shows the finding count.

Now i need to add a avg line to the chart, where i already have the formula (Fact_QCBS[Serial Number] is the product ID)

After i add the avg line to the chart it obviously just lays another line above the exisiting one, due to the relation with product ID.

How can i make a avg line that just shows the plain value as a line, so that it shows as below?

Filtering in the above avg_rating dax?

Best regards

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution dk_dk  offered,and i want to offer some more information for user to refer to.

hello @baneworth ,based on your description, you can try the following code.

AVG_Rating =
VAR a =
    SUMX ( ALLSELECTED ( Fact_QCBS[Serial Numner] ), [Total Rating] )
VAR b =
    CALCULATE (
        DISTINCTCOUNT ( Fact_QCBS[Serial Numner] ),
        ALLSELECTED ( Fact_QCBS )
    )
RETURN
    DIVIDE ( a, b ) + 0

If the solution cannot meet your requirement, can you provide some sample data so that can provide more suggestion for you.

Best Regards!

Yolo Zhu

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

[baneworth]

It worked 🙂

thanks a lot

[dk_dk]

Hi @baneworth 

You could try the folowing change to your formula:

AVG_Rating = [Total rating]/DISTINCTCOUNT(ALL(Fact_QCBS[Serial Number]))+0
You may have to also modify your Total rating measure.