Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi
Below is a sample of a report which I have created in Power BI. The monthly files for this report are uploaded onto one folder stored in the sharepoint and combined into one dataset which serves as the data feeding into this report.
I am trying to create a card visual to calculate the variance of the Value between the current month (or whichever month selected from the dropdown list) vs. December 2018. Please note that I am not looking at YTD data. I want to calculate the values "AS AT" the month selected vs Dec 2018 (which is always static). Also, I need the card to change accordingly whenever the other filters are applied by the user of this report.
I have tried all the ways which I think might work but is still unable to get it to work most likely due to the "Period" as page filter.
Is there any ways I can create a measure to take the values from the column chart below and use them in my calculation? i.e. based on the chart below Value @ April 2019 = 1.58, Value @ Dec 2018 = 1.34, Variance = (1.58-1.34)/1.34 = 17.9%.
Or is there any other ways to get this to work?
Many thanks in advance for your kind assistance.
Solved! Go to Solution.
Hi @Anonymous
Try Allselected() like below.
Impairment_Variance = VAR ImpairmentDec2018 = Calculate( sum('AU Trade Loan Data'[Impairment for Graph]), allselected(), 'AU Trade Loan Data'[Source.Name]="2018-12-AU_Monthly_Reporting.xlsx" ) VAR Impairment_SelectedMonth = sum('AU Trade Loan Data'[Impairment for Graph]) Return if(isblank(Impairment_SelectedMonth)=TRUE,0,Divide (Impairment_SelectedMonth,ImpairmentDec2018)-1)
Hi @Anonymous
Please see the below Dax Expression
Sales Dec 2008 = VAR dec2008 = CALCULATE( [Sales], -- measure you want to calculate ALL( 'Calendar' ), -- calendar table (date table) 'Calendar'[Year Month] = "2008 Dec" -- Calendar table with combined Year and month ) VAR sls = [Sales] -- measure you want to calculate RETURN IF( ISBLANK( sls ) = FALSE, DIVIDE( sls, dec2008 ) -1 )
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks @Mariusz for your prompt reply.
I manage to get the dax to work. Here is the dax I adapted from your suggestion:
Hi @Anonymous
Try the below, make sure you have no page or report filters on category.
Impairment_Variance = VAR ImpairmentDec2018 = Calculate( sum('AU Trade Loan Data'[Impairment for Graph]), all('AU Trade Loan Data'), 'AU Trade Loan Data'[Source.Name]="2018-12-AU_Monthly_Reporting.xlsx" ) VAR Impairment_SelectedMonth = sum('AU Trade Loan Data'[Impairment for Graph]) Return if(isblank(Impairment_SelectedMonth)=TRUE,0,Divide (Impairment_SelectedMonth,ImpairmentDec2018)-1)
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Mariusz ,
That's the problem. I do indeed have the category as a page filter. Is there any workaround?
Regards,
cheols
Hi @Anonymous
Have you tried the code I gave you?
Many Thanks
Mariusz
Hi @Anonymous
Try Allselected() like below.
Impairment_Variance = VAR ImpairmentDec2018 = Calculate( sum('AU Trade Loan Data'[Impairment for Graph]), allselected(), 'AU Trade Loan Data'[Source.Name]="2018-12-AU_Monthly_Reporting.xlsx" ) VAR Impairment_SelectedMonth = sum('AU Trade Loan Data'[Impairment for Graph]) Return if(isblank(Impairment_SelectedMonth)=TRUE,0,Divide (Impairment_SelectedMonth,ImpairmentDec2018)-1)
Hi @Mariusz , yes, I have tried it but the denominator returned for Dec 2018 is not right. It takes the sum of everything even after filters are applied. I need it to be dynamic as the users apply the necessary filters.
User | Count |
---|---|
70 | |
70 | |
34 | |
23 | |
22 |
User | Count |
---|---|
96 | |
94 | |
50 | |
42 | |
40 |