Count amount of rows in one measure that depending on the value of another measure
I am trying to create a crosstable that shows both a total value and amount of posts of a department. The value is no problem since I already have it as a standard calculation in our cube.
But I have tried several different suggestions from ChatGPT to add a calculated column that shows the amount of posts pr. department.
Any suggestions here?
My columns are as folowing: Profitcenter (measure), Bilagsnummer(measure), Beløb(calculation). The "Bilagsnummer" column is gonna be hidden, but the calculation column amount of posts is gonna be visible
0
Comments
Hi René,
It will be easier to assist you if you can provide a screenshot, but I will try anyway.
Try one of these:
allcount(d-1, all(s), m1) or allcount(d-1, all(c), m1)
and see if they calculate the numbers that you need.
BR / Ole
It's working perfect with the formula: allcount(d-1, all(s), m1)
My next problem is as following:
I have hidden the "Bilagsnummer" column, but the table still shows a row pr. Bilag.
How do i make it so, that each "Profitcenter" only has one row and sums up the total amount and amount of bilag?
Hi René,
From your screenshot it seems that you also have hidden the subtotals for Profitcenter. You should probably reenable these subtotals. The subtotal should then give you the correct subtotal "Beløb".
For the subtotals of "Antal bilag" to become correct, you should use the other calculation: allcount(d-1, all(c), m1).
Finally, when the subtotals show the correct values, you can hide everything related to "Bilag" - use the standard Visibility option in the Properties Smartpad.
BR / Ole
Hi Ole
It's working as intended now, thanks :)
Please sign in to leave a comment.