Forum Discussion

BruceAnderson's avatar
BruceAnderson
Helper II
7 years ago
Solved

Group Measure values on rows

I have a simple sales table per customer.  Customer No |  Customer Disc Group | Sales Amt   I need to show a table in Power BI based on it, showing the ranking but in groups.    1-20%       1,54...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi,@BruceAnderson

          After my research, Whether you have used slicer to filter data like below:

    before

    After

    IF so, you need to use ALLSELECTED Function and ALLEXCEPT Function in the measure like this

    Measure 2 = 
    var a=ADDCOLUMNS(Sales_analysis,"r",RANKX (
     ALLSELECTED(Sales_analysis[Customer No]),
     CALCULATE(SUM(Sales_analysis[Sales Amt]),ALLEXCEPT(Sales_analysis,Sales_analysis[Customer No])),
     ,
     0,
     DENSE
    )) return
    var _table=FILTER(GENERATE(a,'Ranking Buckets'),[r]>='Ranking Buckets'[Start]&&[r]<='Ranking Buckets'[End]) return
    CALCULATE(SUMX(_table,[Sales Amt]))

    Result:

    Best Regards,

    Lin

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi,@BruceAnderson

      Your description is not very clear, please share some data sample and expected output and some screenshot for us. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

 

Best Regards,

Lin

  • BruceAnderson's avatar
    BruceAnderson
    Helper II
    7 years ago

    My apologies. This is the data source.  It is very simple, just a customer number and sales amount.


    What we need to do is have it summarized by ranking and the total sales per each ranking as per below.

    Rank Group    Total Sales Amount
    1-20%             1,544,890
    21-50%           1,043,878
    51-150%            903,968
    151-%                648,891

    The method I took was to use asimple table with ranges in it and the below DAX.



    Ranking Group = CALCULATE(VALUES('Ranking Buckets'[Rank]), FILTER(ALL('Ranking Buckets'),Sales_Analysis[Sales Ranking] >= 'Ranking Buckets'[Start] && Sales_Analysis[Sales Ranking] <= 'Ranking Buckets'[End]))

     

    In PowerPivot previously this worked  and I could see all ranking groups.  However in BI the behaviour is different it seems, and everything is lumped into the first group.