Forum Discussion

Ciria's avatar
Ciria
Advocate III
8 years ago
Solved

Cumulative Sales Without Dates on Rows

Dears :smileysad:   These days I am working in building a table with manifolds KPI's to monitor sales evolution. Following Commercial Chief Officer's advices I'm creating two tables, one table lead...
Need Help
Tips and Tricks
Tutorial Requests
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Ciria,

     

    I created a solution based on your sample.

    1. Add an index column in the table "Master_Item_all".

    Index = mid([Family], 8, 3)  // why 3? In case you have "Family 100"

    Cumulative_Sales_Without_Dates_on_Rows

    2. Create a new measure.

    Total Sales Cumulative =
CALCULATE (
    [Total Sales],
    FILTER (
        ALL ( Master_Item_All ),
        Master_Item_All[Index] <= MAX ( Master_Item_All[Index] )
    )
)

    Cumulative_Sales_Without_Dates_on_Rows2

     

     

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi Ciria,

 

How to accumulate sales by Family? For example:

Family     Value

Family 1  100

Family 2  200

Family 3  300

 

Your expected result could be: 

Family 1  Cumulative values

Family 1  100

Family 2  200 + 100 = 300

Family 3  300 + 200 + 100 = 600

 

Is this your desired result?

If so, maybe you can add an index in the Query Editor. Then filter the index like this.

Total Sales Acumulative 2 =
CALCULATE (
    [Total Sales LY];
    FILTER ( ALLSELECTED ( 'Sales' ); 'Sales'[Index] <= MAX ( 'Sales'[Index] ) )
)

Best Regards,

Dale

  • Ciria's avatar
    Ciria
    Advocate III
    8 years ago

    Hi v-jiascu-msft

     

    Thanks for the comment and suggestion, it was definately a good ID, and yes, this is what I am looking for.

     

    Unfortunately it doesn't work, I've tried adding a new Index Column in Sales Table, and creating a new measure in the way you commented.

    It works in cumulative perspective, but it is not taking the sales values properly, values are huge!...

     

     

    Awaiting for your support.

     

    Regards,

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      8 years ago

      Hi Ciria,

       

      Can you share the pbix file? 

      Where is the Family field in the visual from? Try to add the index in the table where the Family is from and try this formula please:

      Total Sales Acumulative 2 =
CALCULATE (
    [Total Sales LY];
    FILTER ( ALLSELECTED ( 'TableOfFamily' ); 'TableOfFamily'[Index] <= MAX ( 'TableOfFamily'[Index] ) )
)

      Best Regards,

      Dale

      • Ciria's avatar
        Ciria
        Advocate III
        8 years ago

        Hi v-jiascu-msft

         

        I cannot upload the original file for personal reasons but I've created this dummy file which conserves most of the difficulties, table's structure, relathionships and modelling.

         

        You will find some quick-calculations / aggregations in the table to evaluate how the measures I've created for this and for another problem I've opened related with ranking purposes, behave.

         

        Note 1: I'm using "Family" from Master_Item_All table, because it helps to get right values on calculations from other fact tables (Budget and Open_Sales).

         

        Note 2: You will see there are three many/fact tables, connected with dimension tables for crossing calculations.

         

        Awaiting for your feedback about this problem and the mentioned in Note 1. Let me know if the link doesn't work, I don't remember very well how to embed a pbix file.

         

        Thanks in advance,:smileyvery-happy:

         

        https://app.powerbi.com/groups/me/reports/6b360cb4-5fa2-4c23-88f2-d6dcb74fd366?ctid=0d6bf08f-27a8-4739-8ad5-d7a6d74ccfca

         

        https://app.powerbi.com/view?r=eyJrIjoiMGU3NGQ1YTMtNTJkMi00NDNjLTg1ZmMtYmY2ZTBiYWVhYjBiIiwidCI6IjBkNmJmMDhmLTI3YTgtNDczOS04YWQ1LWQ3YTZkNzRjY2ZjYSIsImMiOjh9

         

         

        <iframe width="800" height="600" src="https://app.powerbi.com/view?r=eyJrIjoiMGU3NGQ1YTMtNTJkMi00NDNjLTg1ZmMtYmY2ZTBiYWVhYjBiIiwidCI6IjBkNmJmMDhmLTI3YTgtNDczOS04YWQ1LWQ3YTZkNzRjY2ZjYSIsImMiOjh9" frameborder="0" allowFullScreen="true"></iframe>