gran total measure

[Alberto95]

Hi, i've a probem with a grand total measure. the row totals are correct but the grand total no. 

i think the problem is in the filter. attached the structure and the dax formula. 

the grand total is wrog both when i filter by Progetto and when i don't filter the table but is correct when i filter/select one row of TAB_VOCE[chiave_costo_ricavo]

Ricavi su MC Prod =

CALCULATE (SUM(RICAVI[TOTALEImportoVendita]),

FILTER (ALL ( TAB_VOCE ),

TAB_VOCE[chiave_costo_ricavo] = SELECTEDVALUE (RICAVI[chiave_costo_ricavo] )))

/ SUM(RICAVI[MC_Riga_Prev]) * SUM(PRODUCTIONS[mc_tot])

the structure: 

Someone could help me? 

thanks

[Alberto95 · Accepted solution]

Unfortunatly no. i tried some different options without results. (ALL, ALLSELECTED) and i also tried to exclude error or blank row. 

in the attached .pbix link. "test page" is the report page where you can find the problem. 

https://www.dropbox.com/s/jzvdguhx079b2fv/test_dax.pbix?dl=0

Ricavi su MC Prod = CALCULATE (SUM(RICAVI[TOTALEImportoVendita]), FILTER (ALLSELECTED( TAB_VOCE ), TAB_VOCE[chiave_costo_ricavo] = SELECTEDVALUE (RICAVI[chiave_costo_ricavo] ))) / SUM(RICAVI[MC_Riga_Prev]) * SUM(PRODUCTIONS[mc_tot])​

 

thanks to all for solution

[Alberto95 · Accepted solution]

Unfortunatly no. i tried some different options without results. (ALL, ALLSELECTED) and i also tried to exclude error or blank row. 

in the attached .pbix link. "test page" is the report page where you can find the problem. 

https://www.dropbox.com/s/jzvdguhx079b2fv/test_dax.pbix?dl=0

Ricavi su MC Prod = CALCULATE (SUM(RICAVI[TOTALEImportoVendita]), FILTER (ALLSELECTED( TAB_VOCE ), TAB_VOCE[chiave_costo_ricavo] = SELECTEDVALUE (RICAVI[chiave_costo_ricavo] ))) / SUM(RICAVI[MC_Riga_Prev]) * SUM(PRODUCTIONS[mc_tot])​

 

thanks to all for solution

[v-yiruan-msft · Accepted solution]

Hi @Alberto95 ,

I updated your sample pbix file(see the attachment), please check if that is what you want.

1. Create a measure as below 

Measure = 
VAR _tiv =
    CALCULATE (
        SUM ( RICAVI[TOTALEImportoVendita] ),
        FILTER (
            ALLSELECTED ( TAB_VOCE ),
            TAB_VOCE[chiave_costo_ricavo] = SELECTEDVALUE ( RICAVI[chiave_costo_ricavo] )
        )
    )
VAR _mrp =
    SUM ( RICAVI[MC_Riga_Prev] )
VAR _mt =
    SUM ( PRODUCTIONS[mc_tot] )
RETURN
    IF ( ISBLANK ( _mrp ) || _mrp = 0, BLANK (), DIVIDE ( _tiv, _mrp ) * _mt )

2. Update the formula of measure [Ricavi su MC Prod] as below

Ricavi su MC Prod = SUMX(VALUES('TAB_VOCE'[chiave_costo_ricavo]),[Measure])

Best Regards

[v-yiruan-msft]

Hi @Alberto95 ,

Please update the formula of measure [Ricavi su MC Prod] as below and check if it can return your expected result:

Ricavi su MC Prod =
CALCULATE (
    SUM ( RICAVI[TOTALEImportoVendita] ),
    FILTER (
        ALLSELECTED ( TAB_VOCE ),
        TAB_VOCE[chiave_costo_ricavo] = SELECTEDVALUE ( RICAVI[chiave_costo_ricavo] )
    )
)
    / SUM ( RICAVI[MC_Riga_Prev] )
    * SUM ( PRODUCTIONS[mc_tot] )

And you can review the following links for the details about the difference between ALL function and ALLSELECTED function.

The Difference Between ALL And ALLSELECTED DAX Functions In Power BI

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT

If the above one can't help you get the expected result, could you please provide some raw data in your tables (exclude sensitive data) with Text  format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards