Forum Discussion

TomEvers's avatar
TomEvers
Frequent Visitor
9 years ago

aggregate aggravation

I had a report that now doesn't work after I modified it with the latest version of the PBI Desktop.

 

My query has all the required data with all the columns set to Text and DoNotSummarize.  With the exception of two columns that are created that take the value of the Ranking column and add the Product key onto it as a decimal (so that I can get unique numeric sorting on the rank and previously this allowed me to display the rank with out aggregation (and without aggravation too) ;-) 

 

On to the report, it uses a Matrix, and is supposed to show the products sold during the last week in desending order of the chains sales for that product in dollars on one tab and units on another.  

( ProductID (which has a sort by on the chain dollar sales rank in the Rows basket on the visual for Dollars

and a copy of ProductID called Product is used so I can sort it by the Chain Units Sales Rank for the other tab.)

 

Then I put the StoreName in the Columns basket for both tabs.

 

Now here's where the problem I'm running into occurs:

 

Previously I was able to add the StoreSalesDollars,StoreSalesDollarsRank and the StoreQtyOnHand into Values Basket and it worked like a charm.  Today I had to change the source of the data to a text file, and although everything else is the same, I have the options of First, Last, Count(distinct), Count and Quick Calc when I add these columns.  The data doesn't need to be summarized it is exactly the way I want it, but I could maybe live with it except that I'd like to use my own column name without the First, Last... being stuck on it.

 

Any ideas?

 

Thanks.

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi TomEvers

     

    Please post your data in one drive and share the link . Also share the output you expect of the data loaded.

     

    Cheers

    CheenuSing

    • TomEvers's avatar
      TomEvers
      Frequent Visitor
      9 years ago

      I simplified my model a little bit here and left out the Unit ranking, but here is the report:

       

      See how it says First in front of my Fields because I'm forced to pick an aggregate I want my report to not have that in front of the field names.  It makes the column names very wide and with 130 stores I really don't need to make the report harder to read.  

      This was working without the aggregation/aggravation just 2 days ago, but I originally built it using the January version of the Desktop.  

       

      The query just brings in the data promotes the header to columns and it trys to do typeconversions, but I remove them since I want them all to be Text and Do Not Summarize.  I then add the Rank$ Column with this:

      Rank$ = VALUE(AggregationAggravation[ChainSalesDollarsRank]) + VALUE(AggregationAggravation[ProductID4Join])/1000000000 and set the Sort By Column on ProductID to this new column.

       

      And here's the sample data used here:

      ChainSalesDollars,ChainSalesDollarsRank,OnHand,ProductID,ProductID4Join,Sales$,SRank$,StoreName
      50.00,1,0,123456789 Firstbestsellerinchain 86,123456789,50.00,1,0000-Chain
      20.00,2,0,223456789 secondbestsellerinchain 10,223456789,20.00,2,0000-Chain
      10.00,3,0,323456789 Thirdbestsellerinchain 12,323456789,10.00,3,0000-Chain
      50.00,1,0,123456789 Firstbestsellerinchain 86,123456789,5.00,3,0100-Dallas
      20.00,2,0,223456789 secondbestsellerinchain 10,223456789,6.00,2,0100-Dallas
      10.00,3,0,323456789 Thirdbestsellerinchain 12,323456789,7.00,1,0100-Dallas
      50.00,1,0,123456789 Firstbestsellerinchain 86,123456789,1.00,2,0020-Chicago
      20.00,2,0,223456789 secondbestsellerinchain 10,223456789,2.00,1,0020-Chicago
      50.00,1,0,123456789 Firstbestsellerinchain 86,123456789,30.00,1,0101-Seatle
      20.00,2,0,223456789 secondbestsellerinchain 10,223456789,0.50,3,0101-Seatle
      10.00,3,0,323456789 Thirdbestsellerinchain 12,323456789,1.00,2,0101-Seatle