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,

 

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>