Forum Discussion

nightfall's avatar
nightfall
Icon for Helper I rankHelper I
5 years ago
Solved

Dynamic P&L and possible relationship issue

Hello everybody!

I have the following issue:
I import the accounts of my clients (table Conti) to our erp in order to prepare their financial statements.
I wanted to prepare some visual with power bi in addition to it.
In addition to the Conti table, where my clients data are stored, i have the BilancioFULL table, with the general charter of accounts of my software, and the "PdC Datev" table, that connects my clients' accounts to the general charter of accounts of my software:
BilancioFULL[Codice] 1-->* PdC Datev[PdC].
PdC Datev[Conto]1-->*Conti[Conto]
(i merged BilancioFULL[Sottocategoria] and PdC Datev[PdC] in 'Conti' afterwards just to make experiments with this issue, but won't work)
'CE2'[Normalized] is related with 'Conti'[Sottocategoria]

 

The problem is that i would like that when one of the categories in the P&L are highlighted, in another table should be shown the various accounts in that category. If CE2 stays without relationship, the second table won't of course filter anything.

 

However, if i connect the CE2[Normalized]1-->*Conti[Sottocategoria] table, the subtotals in green in the picture disappear, but i am able to filter the accounts in the other table.

 

I think this may happen because the subtotals aren't really "translated" by the relationship, as they are part of the [normalized] column (which is simply the P&L template "trimmed") but don't really exist in PdC Datev[PdC], even though they exist in BilancioFULL...
To circumvent this issue I have tried to set the relationship as non-active so that the 3 subtotals in the measure appear (and they do), plus adding USERELATIONSHIP in the CALCULATE to recreate the deactivated relationship just for the explicit Calculate() in the [Valori CE] measure. however if i then click one of the categories in the P&L table, the accounts won't be filtered in the second table.

 

Do you have any idea on where i am wrong?
PS if i make the relationship active and for the subtotals instead of using the measure i try to put e.g.
CurrentItem = "21) Utile (Perdita) dell'esercizio", "AAA"
it appears and shows correctly "AAA"  so I think it's something related to the relationship... only, how can I get around it?

 

Thank you in advance!
Vittorio
Need Help
  • nightfall's avatar
    nightfall
    5 years ago

    as a follow up, the solution to this issue was switching the focus from the P&L visual to the other "accounts" table: there using an ad-hoc measure:

    IF(
    HASONEVALUE(),
    CALCULATE()

    made the trick.
     
    Hope this can help other neophytes like me on this subject.

    Br,
     
    Vittorio
     

3 Replies

  • nightfall's avatar
    nightfall
    Icon for Helper I rankHelper I
    5 years ago

    as a follow up, the solution to this issue was switching the focus from the P&L visual to the other "accounts" table: there using an ad-hoc measure:

    IF(
    HASONEVALUE(),
    CALCULATE()

    made the trick.
     
    Hope this can help other neophytes like me on this subject.

    Br,
     
    Vittorio
     
  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi nightfall ,

     

    There seems to be no problem with the model, it may be a problem with the measure formulas.

     

     

  • nightfall's avatar
    nightfall
    Icon for Helper I rankHelper I
    5 years ago

    hello v-lionel-msft and thank you for your much appreciated hint!
    I have tried changing the measures, the tables, etc, but without success.

    I am really struggling to get the logic behind, because if i activate the relationship the measures of the subtotals don't work anymore, so there might be somehow an implicit filtering that i am not getting.
    e.g. the first subtotal that points to '_Base'[Reddito operativo] is:

    /* A - B del conto economico*/ CALCULATE ( '_Base'[Risultato d'esercizio], 'BilancioFULL'[Tipo] = "CE", 'Elenco'[Index] < 58 )

    that calls [Risultato d'esercizio] which is

    '_Base'[Ricavi] + '_Base'[Costi]

     that in turn are:

    Ricavi = CALCULATE(SUM(Conti[Saldo finale]), Conti[Natura] = "R", DATESYTD('Date'[Date]))

     

    and 

    Costi = CALCULATE(SUM(Conti[Saldo finale]), Conti[Natura] = "C", DATESYTD('Date'[Date]))*-1

     

    I am really getting crazy i have to admit 🙂

     

    Thank you anyway for your comment.

     

    Vittorio