topic Number of type a and b stores opening by month in DAX Commands and Tips

Number of type a and b stores opening by month

Jay2022 — Mon, 28 Oct 2024 16:48:11 GMT

So This problem is in 2 parts

Firstly I want to calculate accumulative totals of store a and b types opening by end of september, october, nov and so on

one column store type with vaules a,b, c and d another column dates

secondly i have measure which have counted total number of stores (TOTAL_NO_STORES) and another which as counted number os stores c and d (C&D) which have already opened therefore i want to plot number of store a and b's each month add them to the the total already opened stores and divide this by total number of stores so it should go up septemner 80%, october 83% and so on.

Any help greatly appreciated

Re: Number of type a and b stores opening by month

lbendlin — Mon, 28 Oct 2024 22:09:45 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Number of type a and b stores opening by month

Anonymous — Tue, 29 Oct 2024 02:45:10 GMT

Hi Jay2022 ,

Based on the description, please try the following methods:

1.Create the sample table.

2.Create the measure to calculate accumulative totals of store a and b.

Cumulative_StoreA = CALCULATE( COUNTROWS('Table'), FILTER(ALL('Table'), 'Table'[Date] <= MAX('Table'[Date]) && 'Table'[Store Type] = "A" ) )

Cumulative_StoreB = CALCULATE( COUNTROWS('Table'), FILTER(ALL('Table'), 'Table'[Date] <= MAX('Table'[Date]) && 'Table'[Store Type] = "B" ) )

3.Create the new measure to calculate the percentage for each month.

Percentage_Opened = DIVIDE( [Cumulative_StoreA] + [Cumulative_StoreB] + [C&D], [TOTAL_NO_STORES], 0 )

4.The result is shown below.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.