Use a measure to display category values in a matrix, but average values for a single category.

[Anonymous]

Hi, hope someone can help. I want to show values for categories, but for a single category "Nød", the values must be averages for the value in Takst_1 and Takst_2.
My test data is seen in "Rådata" and the desired result in "Nødkald korrigeret i data".

I can solve the problem in my query with GROUPBY and an IF statement that places different values in a calculated column.

But how do you do the same with a measure?

let
    Kilde = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8qhMV9JRCknMLi6JNwSyDHUMQZRjUmqOUqxOtJLf4R0p6AqQ5QNyUtGljZHlkc03ArKMdYxxGo9FHtl4LNLorgcip8ykTOwuR5ZEd7aRjhGyNLqrTXRMcBqNRR7d1cjSsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Indsats = _t, Takst = _t, Værdi = _t, ID = _t]),
    #"Ændret type" = Table.TransformColumnTypes(Kilde,{{"Værdi", type number}}),
    #"Grupperede rækker" = Table.Group(#"Ændret type", {"ID", "Indsats"}, {{"SumVærdi", each List.Sum([Værdi]), type nullable number}, {"Alle", each _, type table [Indsats=nullable text, Takst=nullable text, Værdi=nullable number, ID=nullable text]}}),
    #"Udvidet Alle" = Table.ExpandTableColumn(#"Grupperede rækker", "Alle", {"Takst", "Værdi"}, {"Takst", "Værdi"}),
    #"Tilføjet brugerdefineret" = Table.AddColumn(#"Udvidet Alle", "KorrVærdi", each if [Indsats] = "Nød" then [SumVærdi] / 2 else [Værdi]),
    #"Ændret type1" = Table.TransformColumnTypes(#"Tilføjet brugerdefineret",{{"KorrVærdi", type number}})
in
    #"Ændret type1"

Best regards

Søren

[Anonymous]

Hi @Anonymous ,
Inbuilt Total column of matrix in Power BI Desktop will consider the values as per data table rather than considering the average value. So will have to make another measure for total which will add values of all ID with condition for our particular IndStats to consider average value.

[Anonymous]

Hi @Anonymous ,
Can you explain SELECTEDVALUE query?
and if query is resolved please accept it as a solution😊

[Anonymous]

Hi Muskan

Thank you for the reply.
It seems the SELECTEDVALUE is not working correctly when there is both ID and Indsats as rows in the matrix.

Best regards
Søren

[Anonymous · Accepted solution]

Hello @Anonymous 
I think below mentioned DAX Query may help to resolve the query:

Measure_Værdi =
IF (
SELECTEDVALUE ( 'Rådata'[Indsats] ) = "Nød",
CALCULATE (
DIVIDE ( SUM ( 'Rådata'[Værdi] ), CALCULATE(DISTINCTCOUNT( 'Rådata'[Takst]),ALL('Rådata')) ),
REMOVEFILTERS ( 'Rådata'[Takst] )
),
SUM ( 'Rådata'[Værdi] )
)

Thank You!

[Anonymous]

I have made the measure work perfectly - thank you very much for the help.

But I have discovered another problem - when the measure is used, my totals do not show what they should. (see the table with the query solution).

I think I should have the measure wrapped in an IF statement as suggested in this article: Dealing with Measure Totals - Microsoft Power BI Community 

But I can not quite figure it out - can you help?

Best Regards
Søren