Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
I now have been struggling for a while with this and I am going mad as I'm sure this is something simple.
I have created a table where I have various products, their actual sales, forecasted sales and the % difference between actual sales and forecasted sales (%Change).
I have created a parameter (0-100%) and what I want to do now is to filter the below table so that only the %Change (in absolute value) that are bigger than my parameter value get shown.
The filter that I have created is like this:
Type: Column --> Filter = IF(divide(Parameter[Parameter Value],100)<=abs([%Change]), TRUE,FALSE)
What am I doing wrong? Is it because my columns are dates and thus my filter is calculated differently than my measure?
Thanks!
Solved! Go to Solution.
Hi @dreyz64 ,
This as to do with context I have made a single measure the actuals:
Actuals_Final = SUMX(SUMMARIZE(Sheet1;Sheet1[DATE];"@ACTUALS";[ACTUALS2]);[@ACTUALS])
This is additional on top of the ACTUALS2 so you need to have a intermidiate measure to calculate the values.
Please check if is working correctly on the PBIX file.
If it works you need to do a similar thing for forecast. and then use those measures on the Change %2
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @dreyz64 ,
Is your parameter a % column (formatt as text) or a number formated as percentage?
If it's the last one you should compare with 1 and not with 100, because 100% is just a visual format the actual number is 1.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
My parameter is an integer and then when I do my filter is do divide(parameter, 100) so in the end, it is between 0 and 1.
But for my users they can directly input the whole number.
Hi @dreyz64 ,
can you please share a sample file? is difficul to see based on your image what are the formats values etc. used, and when using measure the context is the most important part.
Changing the context of a measure can make very different result than what we are expecting.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUnfortunately I cannot share a sample as I have confidential data and it would take me ages to make a sample report to share...
I've managed to find a work around where instead of trying to use a Filter, I re-calculate my values
For example, i do: IF abs(%Change ) > Threshold (in %) then sum (actuals)
Same for my forecast values.
This allows me to get the below table.
Now visually the table does what I wanted the filter to do. However the totals are completely messed up and I don't understand why 😞
Example of formula:
Hi @dreyz64 ,
This is refering to the context of your measures, you probably need to use an aggregator function (SUMX) instead of a sum but this depends on the way your model is setup.
Can you make a mockup file with sample data and expected resutl? or share the file trough private message?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHI Felix
I don't find a way to send attachements...
Any idea what I can do? Company blocks dropbox and other sharing sites.
Hi @dreyz64 ,
As you refer it's trough a dropbox, onedrive type of link.
Do you know if it's possible the we tranfers link on your company policies? It's also a chance.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi Felix,
Managed to find a work around:
Here is the sample.
Thanks for your help!
Hi @dreyz64 ,
Can you please tell me what are the corrects value that you should get for the information in the table?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi Felix,
Totals should be the sum of the row for each measure
Hi @dreyz64 ,
This as to do with context I have made a single measure the actuals:
Actuals_Final = SUMX(SUMMARIZE(Sheet1;Sheet1[DATE];"@ACTUALS";[ACTUALS2]);[@ACTUALS])
This is additional on top of the ACTUALS2 so you need to have a intermidiate measure to calculate the values.
Please check if is working correctly on the PBIX file.
If it works you need to do a similar thing for forecast. and then use those measures on the Change %2
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you Felix, it seems to be working.
Would you mind explain to me why it was not working before and what the Summarize and SumX do?
I understand more or less but I don't get why it was not working 😞
Hi @dreyz64
SUMMARIZE returns a summary table for the requested totals over a set of groups. In you case I created a temporary table with dates and the measure calculation to act as my calculation base this will change the line context since instead of getting 10 lines perdate I'm only getting one.
SUMX it's an aggregation formulathat returns the sum of an expression evaluated for each row in a table, in your case what I'm doing is picking up the values for each of the lines for previous table and summing them is like having this calculation made:
Line 1 - 10 - Acumulated - 10
Line 2 - 20 - Acumulated - 30
Line 3 - 10 - Acumulated - 40
In the end I return the 40 if I'm on total level or 10 or 20 if I'm on line level.
Row context is very important when calculating measures and this can be change just by adding or changing filter, columns, measures and all sort of data to your visualizations.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThe table should give similar results as the one on top of the report?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThe Table on the top is with all information. All % Changes regardless of the threshold.
I would like to achieve the table at the bottom where i only see values where % changes is bigger than the threshold in absolute.
I wanted to do this with a filter but could not manage. So now I am summing up each measure if they are respecting the condition abs(% Change) > Threshold.
However the totals are all wrong.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |