Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
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
Solved! Go to 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.
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...
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
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
89 | |
87 | |
77 | |
69 | |
68 |
User | Count |
---|---|
220 | |
128 | |
117 | |
82 | |
77 |