Forum Discussion

KevinMTBI's avatar
KevinMTBI
Frequent Visitor
3 years ago

How to count multiple column values in a matrix table visual to analyze two snapshots

My dataset consists of daily snapshots which show the current state of sales etc. In my report I analyze the differences/deltas between these two snapshots. For example:

- In snapshot 1 (01-02-2023) Project A has a delivery date of 02-02-2023. In snapshot 2 (28-02-2023) Project A has a delivery date of 02-03-2023. This means my project has been moved to another posting period, grom february to march. When I analyze the deltas I see a - in 202302 and a + in 202303 for this project.

 

I want to get insights in this movements of periods by my projects. I put some test data together below. Here I analyze the difference between 01-02-2023 and 28-02-2023. It shows that project 301020101 moved from 202302 to 202301 and some other projects got different monthkeys as well.

 

However, now I only want to see the moved projects, in other words, the projects which show more than 1 monthkey in my Matrix visual.

 

I tried some variations of count measures but these won't do the trick, probably because they run over the 01-02-2023+28-02-2023 dataset instead of the Matrix visual+Sales Delta measure.

 

I think a count on a summarize/summarizetable can work, but I can't figure it out yet. 

 

 

 

 

1 Reply

  • Hi KevinMTBI ,

     

    Try this:

    =
    CALCULATE (
        DISTINCTCOUNT ( data[monthkeys] ),
        ALLEXCEPT ( data, data[projectid] )
    )
    

    this should count the unique monthkeys per project id