Slicer to set amount threshold

Anything related to PowerPivot and DAX Formuale
holograful
Posts: 2
Joined: Thu Jul 04, 2019 2:10 pm

Slicer to set amount threshold

Postby holograful » Thu Jul 04, 2019 2:45 pm

​Hi,

I am using Excel 2016, Power Pivot, and DAX measures (I am a novice at this).
Please see the attach excel file to this message.

In several areas / jurisdictions, I have a series of folios (some duplicates), for which permits were issued, and each permit has a specific amount.

I want to create a Power Pivot and count the distinct folios in the areas, where the permit value is over a specific threshold amount set by the user.

I created a table with the data to summarize:
Area Folios Permits Complete Amount
10 a123 a123_1 no $0
10 a123 a123_2 yes $0
11 b456 b456_1 yes $50,001
11 c789 c789_1 no $110,000
11 a456 a456_1 no $11,000,000
11 c456 c456_1 no $55,000,000
11 d789 d789_1 yes $150,000,000
11 e123 e123_1 no $89,000
14 b123 b123_1 yes $55,000,000
14 c123 c123_1 yes $30,000
14 c123 c123_2 yes $30,000
15 f456 f456_1 no $1,500,000
15 f789 f789_1 yes $0
15 f789 f789_2 no $3,000
15 f789 f789_3 yes $0

I created a disconnected table with a series of thresholds:
AmntThreshold
$0
$50,000
$100,000
$500,000
$1,000,000
$5,000,000
$10,000,000
$50,000,000
$100,000,000

I added both tables to Pivot Table, and I created a series of measures:

SelectedThreshold:=MAX(AmntThreshold[AmntThreshold])
CountFolios:=DISTINCTCOUNT(Folios[Folios])
CountPermits:=COUNTA(Folios[Permits])
MaxAmnt:=Max(AmntThreshold[AmntThreshold])

This is the measure to count the distinct folios in the areas, where the permit value is over a specific threshold amount set by the user:
Folios Amnt Above Threshold:=VAR Max_Amnt = [MaxAmnt]
VAR Count_Folios = [CountFolios]
RETURN
IF(Max_Amnt>=[SelectedThreshold],Count_Folios,0)

I created a pivot table:
Row Labels CountPermits CountFolios Folios Amnt Above Threshold
10 2 1 1
11 6 6 6
14 3 2 2
15 4 2 2
Grand Total 15 11 11

I created a slicer to set the required Amount Threshold using the disconnected table.

MY EXPECTATIONS:
The column "Folios Amnt Above Threshold" in the pivot table does not update when I choose various thresholds in the slicer. See the 2 examples (amount >= 1,000,000 and amount >= 50,000,000), that shows what counts the column should have.

Please check the measures, maybe correct them as necessary, or if you have a better suggestion, please let me know.

Ideally, I would like to have a cell in the excel spreadsheet where the user can input any threshold, and reference that cell in the measure, but from my understanding - after combing the DAX, Power Pivot forums -  it is not possible (maybe I am wrong).

Unfortunately the DAX measures that I created do not work for me (no errors, but does not behave as expected). Can you please help?

Thank you,
Holo
Attachments
Count_Above_Threshold.xlsx
(175.48 KiB) Downloaded 12 times

holograful
Posts: 2
Joined: Thu Jul 04, 2019 2:10 pm

SOLVED - Slicer to set amount threshold

Postby holograful » Wed Jul 10, 2019 10:37 am

Solution provided by Brian Connelly (https://www.pbiusergroup.com)

***
Hello,

No worries, glad to help when I can. The context that you needed was not in the example provided and you needed to first filter your 'Folios' table for only the records above the selected threshold before you count them. Using your same measure, modify it as such:

= VAR Max_Amnt = [MaxAmnt] -- This stays the same, you need to know what the max selected value is based on the slicer.

VAR Count_Folios = CALCULATE(DISTINCTCOUNT(Folios[Folios]),FILTER('Folios','Folios'[Amount]>=[MaxAmnt])) – This was modified. Your using a "Calculate" function. It states count records, but filter the table where the folio amount is greater than or equal to the MaxAmnt value.

RETURN

IF(ISBLANK(Count_Folios),0,Count_Folios) -- You could return just the Count_Folio value, but I check if it was blank and substituted a zero so that the table doesn't show blank.


Warm Regards,

Brian Connelly


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 2 guests

cron