1. TARGIT Community
  2. Community
  3. Frontend, Designer and Consumer
Knowledge Base

Count amount of rows in one measure that depending on the value of another measure

René Nielsen ·

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

4 comments
Sort by DateVotes
  • Ole Dyring

    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

    0
  • René Nielsen

    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?

     

    0
  • Ole Dyring

    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

    0
  • René Nielsen

    Hi Ole

    It's working as intended now, thanks :)

    0
Please sign in to leave a comment.