Total in Matrix not showing expect result correctly

[AvPowerBI]

Hi,

I am currently using a matrix, but I have an issue with the Total Value that is appearing on the column FTF

This is the Dax Measure I have for this

FTF =

DIVIDE (

CALCULATE ( DISTINCTCOUNT ( Data[OrderNo] ), Data[IsFirstTimeFix] =1,Data[VisitFix] IN { "Yes" }

),

CALCULATE (

DISTINCTCOUNT ( Data[OrderNo] ),

Data[VisitFix] IN { "Yes" }

)

)

I am expecting the Total for FTF to be an average between 84.92% and 84.00% but for some reason it is showing a value above these two a value of 85.81%

Any ideas why it would be doing this?

Thanks

[v-kkf-msft]

Hi @AvPowerBI ,

Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[MFelix]

Hi @AvPowerBI ,

Measures are based in context in this case when you calculatethe values based on Area you get the 84 for each one, however when you go to the total line instead of picking up each individual value you are getting the distinct count for all the areas so that is why you are getting a different resutl.

Try the following measure:

FTF =
AVERAGEX (
    VALUES ( Table[Area] ),
    DIVIDE (
        CALCULATE (
            DISTINCTCOUNT ( Data[OrderNo] ),
            Data[IsFirstTimeFix] = 1,
            Data[VisitFix] IN { "Yes" }
        ),
        CALCULATE ( DISTINCTCOUNT ( Data[OrderNo] ), Data[VisitFix] IN { "Yes" } )
    )
)

[AvPowerBI]

Hi @MFelix 

Thanks for your reply, why doesn't the Rows on the Matrix know that it the context?

My issue is that I will duplicating the Matrix but pputting different dimensions in the Rows, so are you saying I have to create a different DAX measure per Dimension i.e. replace the below 

VALUES ( Table[Area] )

 with a different dimension I want to do it based on?

[MFelix]

Hi @AvPowerBI ,

You are correct in your assumption however this depends on the type of data you have.

It's possible to make a single measure depending on the type of information, one possibility is the same has is used in the x-axis dinamic check the blog post below:

https://blog.crossjoin.co.uk/2018/04/20/dynamically-changing-a-chart-axis-in-power-bi-using-bookmark...

This will allow to make matrix instead of chart, and there is also no need to use bookmarks you just need to change the filter in the matrix.

If you are having issue in implementing this can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[AvPowerBI]

Hi @MFelix 

I have uploaded the pbix file at the below onedrive

https://1drv.ms/u/s!AkzJiHUi2WtQefJ1pkS9iSlHzOE?e=ibBe7n

I there are two DAX measures, one being my original FTF and a new one which is based on your DAX FTF 2

I have different sheets where the Rows have different columns

This is what my report will look like where the user wants to see it from different point of view

[MFelix]

Hi @AvPowerBI ,

In this case the best solution would be to unpivot the Area B, C and D and make the calculation based on this however and since I assume you have other calculatios that need this split what you can do is:

• Create a disconnected table with the values AREAB, AREAC, AREAD

AreaTable = 
{"AREAB" ,
     "AREAC" ,
     "AREAD" 
}

• Add the following measure:

Total_Area =
SWITCH (
    SELECTEDVALUE ( AreaTable[Value] ),
    "AreaB",
        AVERAGEX (
            VALUES ( Data[AreaB] ),
            DIVIDE (
                CALCULATE (
                    DISTINCTCOUNT ( Data[OrderNo] ),
                    Data[IsFirstTimeFix] = 1,
                    Data[VisitFix] IN { "Yes" }
                ),
                CALCULATE ( DISTINCTCOUNT ( Data[OrderNo] ), Data[VisitFix] IN { "Yes" } )
            )
        ),
    "AreaC",
        AVERAGEX (
            VALUES ( Data[AreaC] ),
            DIVIDE (
                CALCULATE (
                    DISTINCTCOUNT ( Data[OrderNo] ),
                    Data[IsFirstTimeFix] = 1,
                    Data[VisitFix] IN { "Yes" }
                ),
                CALCULATE ( DISTINCTCOUNT ( Data[OrderNo] ), Data[VisitFix] IN { "Yes" } )
            )
        ),
    "AreaD",
        AVERAGEX (
            VALUES ( Data[AreaD] ),
            DIVIDE (
                CALCULATE (
                    DISTINCTCOUNT ( Data[OrderNo] ),
                    Data[IsFirstTimeFix] = 1,
                    Data[VisitFix] IN { "Yes" }
                ),
                CALCULATE ( DISTINCTCOUNT ( Data[OrderNo] ), Data[VisitFix] IN { "Yes" } )
            )
        )
)

now use the first table to filter out the matrix visualizations.

PBIX file attach.

[AvPowerBI]

@MFelixThanks for this, yes this is the total I want when there is more that one row in the matrix and it has to Average out but can you explain to me why the Total for Total_Area figure for each page would be different? Why would it not be the same? Where for the FTF column the Total value would be the same across all pages but I then have the issue of average it out when there are more than one percentage value i.e. Pages AreaB and AreaD 

AreaB

AreaC

AreaD

Thanks

[MFelix]

Hi @AvPowerBI ,

The total for each page is different because is getting the value for each of the specific areas, if you do the average for AREAD the 84.92 + 84 = 84.46

Do you want to always have the same total average for each of the areas?

[AvPowerBI]

Yes I believe I would want the same Total value on each Page but also want this average of more that one percentage to still show the correct Total value. Not sure if this is possible then? 

[MFelix]

Hi @AvPowerBI,

Sorry but now you have lost me if we take the AREAD what is the values you want to present on your totals?

Is it something like this:

MidCent   - 84,92%

MidSouth - 84,00%

Total          - 84,46%

Total         - 85,81%

[AvPowerBI]

Suppose my question is for the below pecentages for MidCent and MidSouth, why are they are not at a percentage value for it to then Average the Total to be  85.81%

MidCent   - 84,92%

MidSouth - 84,00%

Total          - 84,46%

Total         - 85,81%

[MFelix · Accepted solution]

Because of context, when you have the average of both values it's 84.92% the 85,81% is the total for all the areas.

The incorrect result is not the line by line value but the total value because has refered the context at a total level is different from the context on the line level.

Going to your first question you wrote  that you would expect the the average between the 84,92 and 84 and not the 85.81.

And sorry for asking again but what is the expected result for the total line? the average of the lines that are presented on the matrix or the 85,81% (that is the total for all lines independent of the area)?

---