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've been trying to create a cumulative total of counts of different types by month. I've have 3 columns:
- Date (when it was created)
- Name of type (There are 4 types in total)
- Count (counting 1 for each row)
I would like to create a diagram where it shows the total count by month.
So, it should be going up steadily, but it doesn't...
I've been trying with this for the cumulative total:
Solved! Go to Solution.
Hi, @SJHA
Please check the link down below.
All measures are in the sample pbix file.
Regarding cumulative measures, it is starting from the year 2019 and there is no reset per each year. If you want to reset per each year, then you can simply replace ALL to ALLSELECT.
Please check and let me know whether it is what you are looking for.
https://www.dropbox.com/s/3wt4dc5i2tjjnbe/SJHA%20v2.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @SJHA
Please correct me if I wrongly understood your question.
I am not sure about your data model, but I think you are not using Dim-Date-Table.
If you only use your fact table, and if you try to create a measure using ALL function, it will be complicated to create a cumulate total per type.
Please try to have a Dim-Calendar Table, and then try to write the below measure.
The sample pbix file's link is down below.
count cumulate =
CALCULATE (
SUM ( Data[Count] ),
FILTER (
ALLSELECTED ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
https://www.dropbox.com/s/kgf9mvu9a2jqa3x/sjha.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, I just tried using a dim date table:
This is the result when I'm using a DIM date table
These are my columns: Antal = count and a legend column (faser_simpel)
Hi, @SJHA
Thank you for your feedback.
Sorry to say that it is quite difficult for me to look into the problem by seeing the screencapture.
If it is OK with you, please share your sample pbix file, then I can try to write more accurate measures.
Thank you.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
I think the problem may be that my counts are just 1s.
data file
pbix file with the data
https://www.dropbox.com/s/7qly650zghtjupx/sjha.pbix?dl=0d%20Faser_simpel.csv?dl=0
Hi, @SJHA
Thank you for your sample file.
Please change the data type to date.
The column, Oprettet, is not Date-Type, and it is not properly connected to the dim-date-table.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
https://www.dropbox.com/s/fwpwwzkxgu8zch1/WPA_data_test.pbix?dl=0
This should be correct. I'm sorry the previous example wasn't created correctly with the dates.
Hi, @SJHA
Thank you for your information.
However, your date table is not a date table.
It has a gap in the table, so it cannot be assigned as a date table.
Please create it again, and make it as a date table.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi again @Jihwan_Kim
You said that my "...date table is not a date table.
It has a gap in the table, so it cannot be assigned as a date table.
Please create it again, and make it as a date table."
I don't quite understand what you mean as it works fine from my end when I convert to date datatype - is it a calendar/time settings differences?
This is the data https://www.dropbox.com/scl/fi/7q43ivevh16dl8nnep45g/data_example.xlsx?dl=0&rlkey=y0f121u3gqdebgdxtl...
I would like to know how many of the items are in the stages by month and let it be cumulative.
Would it be possible to get it in percentage per month as well? The percentage of items in stage "Udførelser" out of the total items by the month.
I've been stuck for many hours trying to get it right, so I really appreciate your help here.
Thank you
Hi,
I think you missed one column in the excel file.
There are only Title, Created, Stage.
No column for the numbers.
Please kindly check and re-share the link.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
They count for 1 each, but I've updated the Excel document as well https://www.dropbox.com/scl/fi/7q43ivevh16dl8nnep45g/data_example.xlsx?dl=0&rlkey=y0f121u3gqdebgdxtl...
Hi, @SJHA
Please check the link down below.
All measures are in the sample pbix file.
Regarding cumulative measures, it is starting from the year 2019 and there is no reset per each year. If you want to reset per each year, then you can simply replace ALL to ALLSELECT.
Please check and let me know whether it is what you are looking for.
https://www.dropbox.com/s/3wt4dc5i2tjjnbe/SJHA%20v2.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks a lot for the file. I've learned a couple og things or two already!
Your help is much appreciated.
Hi, @SJHA
Please check the below picture.
I will use your excel file to create the visual. Please wait.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |