Forum Discussion

venug20's avatar
venug20
Icon for Resolver I rankResolver I
7 years ago
Solved

based customer performance - provide rank diff b/w two months

Hi Every one,   I have data like below.....    Aug & Sep month data customer wise sales. When select Sep month in "Slicer"   I want to show employee ranks month wise.....   Sample output like...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    You may refer to my solution in this PBI file.

     

    Hope this helps.

     

     

PattemManohar's avatar
PattemManohar
Icon for Community Champion rankCommunity Champion
7 years ago

venug20 I've tried below steps to solve your scenario...

 

Use "Group By" option in "Power Query Editor" to group customer wise values and the output will look like below

 

AugCustSeptCust

Then, add "New Column" using following DAX expression under "Data" pane

 

AugRnk = RANKX(AugCust,AugCust[Values]) 
SepRnk = RANKX(SepCust,SepCust[Values])

Have a relationship between these two tables using "Customer" field and now use a "Table" visual to have a output like this..

 

Final Output

 

venug20's avatar
venug20
Icon for Resolver I rankResolver I
7 years ago

PattemManohar

 

i want to select "Sep" month in Slicer. you can treat as one table for "Aug" & "Sep". 

 

but should give month selection in "Slicer". based on selection output should be display.

  • PattemManohar's avatar
    PattemManohar
    Icon for Community Champion rankCommunity Champion
    7 years ago

    venug20 I didn't really get what you want with the slicer ? That means, if you select "Sep" in slicer then you want to show Sep and Aug. If you select "Aug" then you want to show Aug and Jul  OR you need to show Aug and Sep (Just order of columns preference)...

     

    Please elaborate a bit....

    • venug20's avatar
      venug20
      Icon for Resolver I rankResolver I
      7 years ago

      PattemManohar

       

      Yes, treat "Aug" & "Sep" as one table (Not Two tables). i want to show "Aug" & "Sep" months in "Slicer".

       

      When i select "Sep", data shows for "Aug" & "Sep" months with rank customer wise.

       

      When i select "Aug" data should be show for "Aug" & "July" months with rank customer wise. (This part, No need now, there is  no data)

       

       

       

       

      • v-frfei-msft's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi venug20,

         

        I made one sample for your reference.

         

        1. Create a calculated table using the formula and create retaltionship between it and teh fact table.

         

        Table = DISTINCT(Table1[Customer])



        2. Create the measures as below.

         

        Measure = SUM(Table1[Values])
        rankall = RANKX(ALL('Table'),[Measure],,DESC,Dense)

        For more details, please check the pbix as attached. Please notice here we cannot meet the requirement as you said: "When i select "Sep", data shows for "Aug" & "Sep" months with rank customer wise." We can select slicer multi options to work around.

         

        Regards,

        Frank