Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
juanso14
Helper I
Helper I

Problems to calulate a sum of sales with parent.

Hello , im working in Power BI with dynamics 365, and i have some accounts that are parent from another in one table, and I have the total sales for the parent account in another tabler and the % of sales of the child for the total of the parents (the % is on the account table). I Have to multiply the partent sale by the %, but the problem is that same account dont have subsidiary and in that case de % is in blank, is any formula for that?

 

The problem is that i Have an ID for the account and the relationship of the parent with another column. And the sale table only have the name (no the id).

 

I made this caluculation

 Ventas Calculado x % = CALCULATE(SUM('Ventas 17-18'[Ventas Total USD]))

But when i do that a lot of account bring me the same number (493174)

 

 

Tablero.JPGTablero2.JPG

 

Thanks, Best Regards

4 REPLIES 4
v-shex-msft
Community Support
Community Support

HI @juanso14,

 

Do you mean use current account id to find out its parent account, then calculate the total sales of parent list?
If this is a case, you can refer to below measure formula:

Ventas Calculado x % =
VAR current_account =
    SELECTEDVALUE ( 'Ventas 17-18'[accountid] )
VAR _parent =
    CALCULATETABLE (
        VALUES ( 'Ventas 17-18'[_parentaccount_value] ),
        FILTER ( ALL ( 'Ventas 17-18' ), [accountid] = current_account )
    )
RETURN
    CALCULATE (
        SUM ( 'Ventas 17-18'[Ventas Total USD] ),
        FILTER ( ALL ( 'Ventas 17-18' ), [accountid] IN _parent )
    )

If above not help, please share some sample data or pbix file for test.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hello, thank you for the response, i made the following formula:

 

Ventas Calculado x %2 =
VAR current_account =
SELECTEDVALUE ( Cuentas[accountid] )
VAR _parent =
CALCULATETABLE (
VALUES ( Cuentas[_parentaccountid_value] ),
FILTER ( ALL ( Cuentas), [accountid] = current_account )
)
RETURN
CALCULATE (
SUM ( 'Ventas 17-18'[Ventas Total USD] ),
FILTER ( ALL ( Cuentas ), [accountid] IN _parent )
)

 

 

But the results were not ok. Look:

 

tablero 3.JPG

I attach a pbix example. PBIX

 

Thanks

Best Regards

 

Hi guys, 

 

@juanso14 you can try TREATAS function like below:

 

CALCULATE(SUM('Ventas 17-18'[Ventas Total USD]);
        TREATAS(VALUES(COLUMN_NAME_ORIGIN);COLUMN_NAME_SALES))

 

Best regards,

Hi Thyago, that partialy works, but only give me the sale of the parent, now i hace to multiply by the % of the subsidiary. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.