SWITCH measure not totaling correctly in table

[Zosy]

Hi,

I am hoping someone will see what I did wrong and help me correct it. I want to have the total points under the Total column. At the moment it displays 0 instead of 50.

Points = SWITCH(
    TRUE(),
    [Percentage]>0 && [Percentage]<0.7, 0,
    [Percentage]>=0.7 && [Percentage]<0.8, 10,
    [Percentage]>=0.8 && [Percentage]<0.9, 15,
    [Percentage]>=0.9 && [Percentage]<1, 20,
    [Percentage]>=1,25,
    0)

Thank you

[Anonymous]

Hi @Zosy ,

Based on the description,  could you provide the sample data? Is percentage a row or a measure? Besides, the Qtr whether is column or a measure.

Best Regards,

Wisdom Wu

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

[Zosy]

Hi @Anonymous ,

The percentage is a measure and the Qrt is a field coming from the Date table, splitting the Sales, Target measures by quarters in the matrix. Here is the link for the file.
SWITCH measure table total incorrect.pbix 

Thank you

[Anonymous · Accepted solution]

Hi @Zosy ,

I can not open the file link.

Based on my testing, please try the following methods again:

1.Create the sample table.

2.Create the new measure to calculate the point.

Points = 
SWITCH(
    TRUE(),
    [Percentage] > 0 && [Percentage] < 0.7, 0,
    [Percentage] >= 0.7 && [Percentage] < 0.8, 10,
    [Percentage] >= 0.8 && [Percentage] < 0.9, 15,
    [Percentage] >= 0.9 && [Percentage] < 1, 20,
    [Percentage] >= 1, 25, 0)

3.Create the new measure to sumx.

Measure 2 = SUMX(VALUES('Table'[Date]), [Points])

4.Create the new measure to display total.

Display Value = 
SWITCH(
    VALUES('Table t'[Column1]),
    "Sales", [Sales],
    "Target", [Target],
    "Percentage", [Percentage],
    "Points", [Measure 2]
)

5.Drag the measures into the matrix visual.

6.The result is shown below.

Best Regards,

Wisdom Wu

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

[Zosy]

Thank you for your help! It worked brilliantly !

[Greg_Deckler]

@Zosy First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[Zosy]

Hi @Greg_Deckler ,

I had a look at the materials but I can't make the measure work. I don't know what I should change.

Points = 

VAR PointsQuarter = SWITCH(
    TRUE(),
    [Percentage]>0 && [Percentage]<0.7, 0,
    [Percentage]>=0.7 && [Percentage]<0.8, 10,
    [Percentage]>=0.8 && [Percentage]<0.9, 15,
    [Percentage]>=0.9 && [Percentage]<1, 20,
    [Percentage]>=1,25,
    BLANK())
VAR _table= SUMMARIZE('Date','Date'[Quarter],"_value", PointsQuarter)

    RETURN

    IF(HASONEVALUE('Date'[Quarter]),PointsQuarter,SUMX(_table,[_value]))