keep measure value - ignore column fields and filter in matrix

[rezaaditia]

Hi All -

I hope i can explain with well.

I have measure to get last 12 month data. I go t the formulat from below

https://www.ehansalytics.com/blog/2019/3/3/calculate-last-twelve-months-using-dax

This is my dax formula

Spoiler

CALCULATE(COUNT(Details[document])~CALCULATETABLE('Calendar'~FILTER('Calendar'~[Dates] = TRUE())))

based on above measure, lets say 

John A = 1,000

John B = 2,000

John C = 5,000

in same table, now i want to calculate aging bucket vs total

John A has 1,000 tickets where 740 completed and 260 in progress, and below the aging bucket. 

   0-2 days   -> 200

   3-10 days ->  50

   >10 days  ->  10 

Using matrix visualization, i want to know what is the percentage of John A's aging.

my expectation

0-2 days => 200 / 1,000 = 10%

3-10 days=> 50 / 1,000 = 2.5%

etc

now the issue that i faced, the denominator is got filtered based on the aging bucket, what i want the measure will keep 1,000 or 2,000 or 5,000 etc.

how to keep the denominator will use same measure (ignore the column and filters)

Thanks

[amitchandak]

To help you further I need pbix file. If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[rezaaditia]

Hi @amitchandak 

please see below screenshot.

basically all last 12 month value (left/yellow) i want to put the value based last 12 month (right). Ignoring any filters or column in Matrix visualization.

sample pbix https://www.dropbox.com/s/acr6p8fr5owaek1/test.pbix?dl=0

really appreciate it with your helps

Thanks

[v-eachen-msft · Accepted solution]

Hi @rezaaditia ,

After my tests, you need to edit your measure like the following DAX:

Last 12 Month =
CALCULATE (
    COUNT ( [Number] ),
    CALCULATETABLE ( 'Calendar', FILTER ( 'Calendar', [PO Dates] = TRUE () ) ),
    ALL ( ref_OS_bucket )
)

Use ALL() function to remove the filter. Here is the result.

[rezaaditia]

Thank you so much @v-eachen-msft , its really working perfectly