Forum Discussion

D_Brouwers's avatar
D_Brouwers
Regular Visitor
2 years ago
Solved

Calculate Distinct Values per Month/ Year (Running total?)

Hi everyone,
This is my first post, as I've used this form many times to help me out, but I cannot find a fitting solution this time. 
I have a visual in which I'd like to display the unique number of products that are checked per month and the running total (of unique products that are checked) for that year.
The number of unique products is calculated with a simple measure:

Aantal Unieke artikelen (YtD) =
CALCULATE (
    DISTINCTCOUNT ( 'Voorraad vs Scanning'[Artikel] ),
    FILTER (
        'Kalender',
        'Kalender'[Jaar] = 2023
    )
)

What I can't get working is the running total. I've tried 
SUMX(VALUES( 'Voorraad vs Scanning'[Artikel] ), 'Voorraad vs Scanning'[Artikel])
but that ends up with the error that SUMX cannot work with values of type string. I cannot change the type as product ref's are alphanumerical.
Any help is appreciated and the pbix can be found here (version2)
  • D_Brouwers's avatar
    D_Brouwers
    2 years ago

    In the meantime I've found a solution offline that seems to work and which I'm happy to share.
    As a first step I've added a calculated column to my table;

    FirstOccurrence =

    VAR CurrentYear = YEAR('Voorraad vs Scanning'[Datum])

    VAR CurrentValue = 'Voorraad vs Scanning'[Artikel]

    RETURN

    IF(

        MINX(

            FILTER(

                'Voorraad vs Scanning',

                'Voorraad vs Scanning'[Artikel] = CurrentValue &&

                YEAR('Voorraad vs Scanning'[Datum]) = CurrentYear

            ),

            'Voorraad vs Scanning'[Datum]

        ) = 'Voorraad vs Scanning'[Datum],

        CurrentValue,

        BLANK()

    )

    As a final step I've counted the number of non-blank occurence of this "FirstOccurrence".

5 Replies

  • some_bih's avatar
    some_bih
    Icon for Community Champion rankCommunity Champion
    2 years ago

    Hi D_Brouwers 

    usually, SUMX summing some numberical column, like quantity, price... check link for official documentation

    your model is not available, so maybe you need COUNTAX

  • D_Brouwers's avatar
    D_Brouwers
    Regular Visitor
    2 years ago

    Hi some_bih,

     

    I've tried to attach the pbix for reference (I hope it worked). I've also had a look at COUNTAX, but couldn't get that to provide the desired outcome. So....if anyone has any additional suggestions, I'd love to hear them.

  • D_Brouwers's avatar
    D_Brouwers
    Regular Visitor
    2 years ago

    Hello All,

    Link has been updated. This time it should work.

  • D_Brouwers's avatar
    D_Brouwers
    Regular Visitor
    2 years ago

    In the meantime I've found a solution offline that seems to work and which I'm happy to share.
    As a first step I've added a calculated column to my table;

    FirstOccurrence =

    VAR CurrentYear = YEAR('Voorraad vs Scanning'[Datum])

    VAR CurrentValue = 'Voorraad vs Scanning'[Artikel]

    RETURN

    IF(

        MINX(

            FILTER(

                'Voorraad vs Scanning',

                'Voorraad vs Scanning'[Artikel] = CurrentValue &&

                YEAR('Voorraad vs Scanning'[Datum]) = CurrentYear

            ),

            'Voorraad vs Scanning'[Datum]

        ) = 'Voorraad vs Scanning'[Datum],

        CurrentValue,

        BLANK()

    )

    As a final step I've counted the number of non-blank occurence of this "FirstOccurrence".