Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Report Table View Different from Data View

I have a table which can have duplicate transaction ID's because of manual comments that can be made by the user. My problem is that the measure that I created is correctly counting the transaction ID's that I am looking for in the Data View but if I bring that same data into a Report Table Visual, the number of rows get summarized (duplicate transaction ID's get summarized into 1) resulting in total number of rows being smaller than the measure. I tried making sure to "Not Summarize" each column but this is still happening when adding dimensions into a report table.

 

Is there a way for me count the number of transactions that are summarized as 1 transaction? Below is the measure + sample data (current data view output / expected report table output). Goal is to have measure count number of rows like "Expected Report Table".

 

Any advice will be greatly appreciated. 

 

Measure (The goal of the measure is to get transactions from today -120 days not including today)

 

CALCULATE(
    [Transaction Count],
    FILTER(
        Transactions,
        Transactions[Date] > TODAY() -120
    )
)

[Transaction Count] = COUNT(Transactions[trans_id])

 

 

Current Data View Output (Notice trans_id 1 & 5) > Current measure counts this number of rows

trans_iditemcomments
1PizzaCustomer paid in cash
1PizzaCustomer paid in cash
2Burger 
3TacosSteak and Pork Tacos
3TacosCustomer has special request
4Wings 
5PizzaCustomer wants Pineapple topping
5PizzaCustomer wants Pineapple topping

 

Expected Report Table Visual Output (Notice trans_id 1 & 5) > Goal is to have measure count number of rows for this Report Table Visual

trans_iditemcomments
1PizzaCustomer paid in cash
2Burger 
3TacosSteak and Pork Tacos
3TacosCustomer has special request
4Wings 
5PizzaCustomer wants Pineapple topping

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    5 years ago

    I found my own solution by updating the inner measure [Transaction Count]. This will summarize transactions that have the same comment but individually count transactions that have distinct comments.

    [Transaction Count] =
COUNTROWS(
    SUMMARIZE(
        Transactions,
        Transactions[trans_id],
        Transactions[comments]
    )
)

     

10 Replies

  • KNP's avatar
    KNP
    Super User
    5 years ago

    Hi Anonymous - What is the calculation for [Transaction Count]?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      KNP sorry...I forgot to add that. The calculation for Transaction count = COUNT( trans_id )

       

      Thank you for your support

      • KNP's avatar
        KNP
        Super User
        5 years ago

        Possibly, DISTINCTCOUNT( trans_id ) will work, depending on what else you're using that measure for.

         

        Edit: may need to see data model with relationships if it isn't that simple.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    v-yalanwu-msft Greetings. Would you be so kind as to provide any advice if possible?

     

    [Transaction Count] = COUNT( trans_id )

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I found my own solution by updating the inner measure [Transaction Count]. This will summarize transactions that have the same comment but individually count transactions that have distinct comments.

    [Transaction Count] =
COUNTROWS(
    SUMMARIZE(
        Transactions,
        Transactions[trans_id],
        Transactions[comments]
    )
)