Matrix Percentages - hasonevalue issue

[Pete673]

Hi guys
Haven’t posted for some time. So either my CFO day job is taking up too much time or I’m getting competent at DAX. I’d go for the former!

I have a Matrix issue where I can’t get the grand total to calculate correctly. Matrix is an analysis of the components of a car deal had has various % calculations which are correct at line detail and subtotal level but the grand total calculation is rubbish.

This is what I have:
VSpecPercList =

if(
    HASONEVALUE( VSB_VSpec[SequenceInVehicle] ),
    sum( VSB_VSpec[PercList] ),
    SWITCH(
        TRUE(),
        max( VSB_VSpec[VSpecCat] ) = "Standards", sum( VSB_VSpec[PercList] ),
        HASONEFILTER( VSB_Data[Suffix] ),
        Divide(
            sum( VSB_VSpec[VSpecProfit] ),
            sum( VSB_VSpec[InvoiceValue] ),
            blank()
        ),
        Divide(
            Sum( VSB_VSpec[VSpecProfit] ),
            Sum( VSB_VSpec[InvoiceValue] ),
            blank()
        )
    )
)

I need a straight Divide of VSpecProfit/ invoice value

Apologies for Dax formatting below, couldn;t get the formatter to paste.

Looking fwd to help

Pete

[Melissa]

Hi @Pete673,

Nice to read you’re making good progress.
@Greg has created a topic in the DAX Patterns category on fixing totals. You’ll find it here.

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

If you need further assistance please provide a sample PBIX.

[Pete673]

I was on the right track but using a different calc for VSpecCat “Standards”.
If I remove the totals at that level then everything is fine, but I don’t get a standards total %.
and we can leave with that till another day.

Thanks everyone for help

Pete

[BrianJ]

@Pete673,

Great to hear that DAX is "clicking " for you and you were able to solve your own problem.

Matrix totals and subtotals can be a big challenge because even in the simplest case, you have four different evaluation contexts to deal with. Here’s a detailed post I put together on this a while back that I think may provide a useful framework for you to work through your remaining issues.

Sales forecasting with Ramp Up Formula Part 2

@msedlak, Okay, think I’ve got a solution for you on this one as well. Let’s start with the totals first, since that ended up being the harder part - once we get that, you’ll see the stages calculation just becomes a drag-and-drop. The challenging aspect of correcting totals in a matrix is that you have four different evaluation contexts to deal with and correct. The way I handled this was with a SWITCH(TRUE() ) construct to manage the four different combinations of HASONEVALUE = TRUE/FALSE …

• Brian

P.S. Thanks for trying to format your posted DAX. Here’s a trick - if it won’t format, try adding a hard return after the “=” in the first line of the measure and then try to format everything after that hard return line. That worked perfectly when I tried to reformat your posted measure.