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.
Hello
I have a table where i calculate the changes in number of members from one month to another.
There is one measure for the change in number of members
Förändring antal aktiva medlemmar = SUM('Månadsrapportering'[Aktiva medlemmar totalt]) - CALCULATE(SUM('Månadsrapportering'[Aktiva medlemmar totalt]);PARALLELPERIOD('Månadsrapportering'[Period];-1;MONTH))
And one measure for changes in %.
Förändring aktiva medlemmar procent = ([Förändring antal aktiva medlemmar] / CALCULATE(SUM('Månadsrapportering'[Aktiva medlemmar totalt]);PARALLELPERIOD('Månadsrapportering'[Period];-1;MONTH))) *100
The measures works as expected row by row, but when it comes to the total of the % measure, something goes wrong. It seems like it does not add negative numbers correctly.
In the 5th column in the picture above i would expect the total of -0.32.
In the 6th column i try to aggregate the changes and here you can see that the problem is the same. Instead of calculating -0.21 + -0.07 = -0.29 It seems like it is calculated like -0.21 - -0.07 = -0.14
The formula for the measure in column6 is: Aggregerad förändring procent = TOTALYTD([Förändring aktiva medlemmar procent];'Månadsrapportering'[Period])
Probably i am doing some beginners mistake with the measures, but i can not figure out what.
BR
Solved! Go to Solution.
Hi @boxen_solution,
In the 5th column in the picture above i would expect the total of -0.32.
You may need to add an individual Date table which contains each identical [Period](you can use CALENDARAUTO function to create it), and then create a relationship between the Date table and your 'Månadsrapportering' table first.
Date = CALENDARAUTO()
Then you should be able to create a new measure by putting your [Förändring aktiva medlemmar procent] measure within the SUMX function like below to get your expected result in this scenario.
New measure = SUMX ( Date, [Förändring aktiva medlemmar procent] )
In the 6th column i try to aggregate the changes and here you can see that the problem is the same. Instead of calculating -0.21 + -0.07 = -0.29 It seems like it is calculated like -0.21 - -0.07 = -0.14
Most time intelligence functions(including TOTALYTD) require an individual and continuous Calendar table to work correctly, which may cause the issue. Could you try the formula below to see if it works?
Aggregerad förändring procent = TOTALYTD([Förändring aktiva medlemmar procent];'Date'[Date])
Regards
Hi @boxen_solution,
In the 5th column in the picture above i would expect the total of -0.32.
You may need to add an individual Date table which contains each identical [Period](you can use CALENDARAUTO function to create it), and then create a relationship between the Date table and your 'Månadsrapportering' table first.
Date = CALENDARAUTO()
Then you should be able to create a new measure by putting your [Förändring aktiva medlemmar procent] measure within the SUMX function like below to get your expected result in this scenario.
New measure = SUMX ( Date, [Förändring aktiva medlemmar procent] )
In the 6th column i try to aggregate the changes and here you can see that the problem is the same. Instead of calculating -0.21 + -0.07 = -0.29 It seems like it is calculated like -0.21 - -0.07 = -0.14
Most time intelligence functions(including TOTALYTD) require an individual and continuous Calendar table to work correctly, which may cause the issue. Could you try the formula below to see if it works?
Aggregerad förändring procent = TOTALYTD([Förändring aktiva medlemmar procent];'Date'[Date])
Regards
Hello again
Thank you so much for helping me out! I got help in another topic also and toghether i get closer and closer to complete my first reports.
Now i have a similar problem (with the total calculation) This time it is connected to a TOTALYTD function. This is how it looks like in the report:
You see that both the totals for "Aggregerad förändring" and "Aggregerad förändring procent" is wrong.
These are the formulas:
Aggregerad förändring = TOTALYTD([Förändring aktiva medlemmar];'DimDate'[Datum])
Aggregerad förändring procent = TOTALYTD([Förändring aktiva medlemmar procent];'DimDate'[Datum])
I have tried with different approaches using SUMX etc.. but i can get it right. Could you help me?
Best Regards
Mike
Thank you so much for the help! The only think remaining now in my report is to get the date-column formatted in Swedish.
Do you have any clue on where to configure this?
Best Regards
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 |
---|---|
107 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |