% of total issue using custom slicer

DAX DAX Calculations
1

As you can see from the screenshot, the “period % of total lines” is calculating for all dates. I want to be able to use the slicer and I’m struggling to get the right formula. I have tried ALL, ALLSELECTED, etc and either get 100% per line or the % based on all periods. I basically want the results in the first two columns (S2K Total Lines and S2K % of Total Lines) to be dynamic with the period slicer (Period Order Lines and Period % of Total Lines)

OLD - I used this with a relative filter on date column - this worked fine but I was stuck with a relative time period:

S2K Total Lines = CALCULATE(COUNT(fact_S2K_Sales_Master[Order Number]),fact_S2K_Sales_Master[Order Type] = “O”)

S2K % of Total Lines =
DIVIDE([S2K Total Lines],
CALCULATE(COUNT(fact_S2K_Sales_Master[Order Number]),
fact_S2K_Sales_Master[Order Type] = “O”,
ALLSELECTED()))

NEW with Date range slicer (past 3M, 6M, 12M) - NOT WORKING

Period Order Lines =
CALCULATE( [S2K Total Lines],
FILTER(dim_Dates,
dim_Dates[Date] > TODAY() - [Selection Days] && dim_Dates[Date] <= TODAY() ))

Period % of Total Lines =
DIVIDE([Period Order Lines],
CALCULATE(COUNT(fact_S2K_Sales_Master[Order Number]),fact_S2K_Sales_Master[Order Type] = “O”,
ALLSELECTED()))

PBIX is too large to share but I have added measures above. Thanks!

image
image1920×1020 105 KB

2

Hi @grjohnsonjr, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

3

Hello @grjohnsonjr .

Thank you very much for posting, it was a lot of fun to browse the solution.

image
image1004×570 40.9 KB

The measures I used in the test are:

S2K Total Lines

S2K Total Lines = CALCULATE( COUNT(‘Pipeline Stage Data’[History Sort Key]),
FILTER(‘Pipeline Stage Data’, ‘Pipeline Stage Data’[OldValue]= “Contract Sent”))

S2K% total de linhas 3LM = CALCULATE ([S2K% total de linhas],
PARALLELPERIOD(Dates[Date], -3 ,MONTH))

S2K% total de linhas 6LM =
CALCULATE ([S2K% total de linhas],
PARALLELPERIOD(Dates[Date], -6 ,MONTH))

S2K% total de linhas 12LM =
CALCULATE ([S2K% total de linhas],
PARALLELPERIOD(Dates[Date], -12 ,MONTH))

Display Measure S2K total de linhas =
IF( [Seleted Measure] = “Past 3M”, [S2K total de linhas 3LM],
IF( [Seleted Measure] = “Past 6M”, [S2K total de linhas 6LM],
[S2K total de linhas 12LM]))

S2K% total de linhas

S2K% total de linhas =
DIVIDE([S2K Total Lines],
CALCULATE([S2K Total Lines], ALLSELECTED ()))

S2K% total de linhas 3LM =
CALCULATE ([S2K% total de linhas],
PARALLELPERIOD(Dates[Date], -3 ,MONTH))

S2K% total de linhas 6LM =
CALCULATE ([S2K% total de linhas],
PARALLELPERIOD(Dates[Date], -6 ,MONTH))

S2K% total de linhas 12LM =
CALCULATE ([S2K% total de linhas],
PARALLELPERIOD(Dates[Date], -12 ,MONTH))

Display Measure S2K% total de linhas =

IF( [Seleted Measure] = “Past 3M”, [S2K% total de linhas 3LM],
IF( [Seleted Measure] = “Past 6M”, [S2K% total de linhas 6LM],
[S2K% total de linhas 12LM]))

The big secret here is this table

image

Create it using this option
image

The measures I used in this table is:
Seleted Measure = SELECTEDVALUE(‘Period support table’[Descrition], “Past 3M”)

Finds download files
Pipeline Stage Data.xlsx (68.8 KB)
Pipeline Stage Data.pbix (149.9 KB)

In case of any doubt please do not hesitate to contact.

Best regards
Gifted

1 Like