Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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]
the structure:
Someone could help me?
thanks
Solved! Go to 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
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
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
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
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
90 | |
89 | |
79 | |
70 | |
68 |
User | Count |
---|---|
226 | |
129 | |
120 | |
84 | |
78 |