Getting proper total in table visualization

[StefanieCarroll]

Hello:  I am brand new to this so this might be a simple question, but I've scoured all sorts of solutions but none quite work for what I'm trying to achieve.  I have a simple customer table showing a supply requirement (DCQ), units bought in NOV19 and what is required still to buy.  Currently I have the following measure for "required" and I'm trying to solve for that last part "999" to get the correct total in my total line:

Required = if(HASONEFILTER('Prompt month DCQ Noms'[Customer]),max(sum('Prompt month DCQ Noms'[DCQ no balancing])-sum('DT Download'[Nov19]),0),999)

Data looks like this:

Hopefully this makes sense.  Any help would be appreciated.

Thanks.

[Icey · Accepted solution]

Hi @StefanieCarroll ,

You can create measures like so:

Measure =
MAX (
    SUM ( 'Prompt month DCQ Noms'[DCQ no blancing] ) - SUM ( 'DT Download'[Nov19] ),
    0
)

Required =
IF (
    HASONEFILTER ( 'Prompt month DCQ Noms'[Customer] ),
    [Measure],
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE ( 'Prompt month DCQ Noms', 'Table'[Customer] ),
            "required", [Measure]
        ),
        [Measure]
    )
)

Best Regards,

Icey

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

[StefanieCarroll]

Thank you very much!!  Now to fully understand these formulas so I can make it even more complicated!  haha... but you have set me on the right track.  🙂