Measure Total not reflecting correctly

[Amitkr174]

Hi,

When I am putting a measure (Name - Revenue) in a table, the grand total is not reflecting correctly. Any advise on this would be appreciated?

I am attaching the Power Bi total screenshot and Excel total screenshot (Actual revenue).

Power Bi Desktop screenshotActual total screenshot

[v-cherch-msft · Accepted solution]

Hi @Amitkr174 

You may try modify below measure.Here is the similar post for your reference.If you need further help,please share your sample file.You can upload it to OneDrive and post the link here.

Measure =
SUMX (
    SUMMARIZE ( Budget, Budget[Project Name], Budget[Budget Name]... ),
    DIVIDE (
        [Est Vs Actuals Actual Billable Amount USD Std Total],
        [New Pacing% for a WK]
    )
)

Regards,

[v-cherch-msft]

Hi @Amitkr174 

You may add a measure like below:

Measure =
SUMX ( VALUES ( Table[Project Region] ), [Revenue] )

Regards,

[Amitkr174]

Hi,

Thanks for the DAX.  It is working at the total level. But when I drill though to other page the total is not matching again. Please see the below screenshots. At the total level sum is 491K while  the total of drill though page is 480K (as shown in excel total screenshot).

Measure used to get Revenue :-  SUMX(VALUES(Budget),DIVIDE([Est Vs Actuals Actual Billable Amount USD Std Total],[New Pacing% for a WK]))

Measure is in budget table.

Drill through

[v-cherch-msft · Accepted solution]

Hi @Amitkr174 

You may try modify below measure.Here is the similar post for your reference.If you need further help,please share your sample file.You can upload it to OneDrive and post the link here.

Measure =
SUMX (
    SUMMARIZE ( Budget, Budget[Project Name], Budget[Budget Name]... ),
    DIVIDE (
        [Est Vs Actuals Actual Billable Amount USD Std Total],
        [New Pacing% for a WK]
    )
)

Regards,

[Amitkr174]

Still the total is not matching 

DAX used:-

---

@v-cherch-msft wrote:

Hi @Amitkr174 

You may try modify below measure.Here is the similar post for your reference.If you need further help,please share your sample file.You can upload it to OneDrive and post the link here.

Measure =
SUMX (
    SUMMARIZE ( Budget, Budget[Project Name], Budget[Budget Name]... ),
    DIVIDE (
        [Est Vs Actuals Actual Billable Amount USD Std Total],
        [New Pacing% for a WK]
    )
)

Regards,

---

---

@v-cherch-msft wrote:

Hi @Amitkr174 

You may try modify below measure.Here is the similar post for your reference.If you need further help,please share your sample file.You can upload it to OneDrive and post the link here.

Measure =
SUMX (
    SUMMARIZE ( Budget, Budget[Project Name], Budget[Budget Name]... ),
    DIVIDE (
        [Est Vs Actuals Actual Billable Amount USD Std Total],
        [New Pacing% for a WK]
    )
)

Regards,

---

SUMX (

SUMMARIZE (Budget, Project[Project Name], Budget[Budget Name]),

DIVIDE (

[Est Vs Actuals Actual Billable Amount USD Std Total],

[New Pacing% for a WK]

)

)

[Amitkr174]

Hi,

DAX is working fine now. Is there anyway to optimize it as it is making the file slow as I have thousands of rows.

Thanks

[v-cherch-msft · Accepted solution]

Hi @Amitkr174 

You may have a look at below articles to optimize it for your scenario.

https://www.sqlbi.com/articles/optimize-many-to-many-calculation-in-dax-with-summarize-and-cross-tab...

https://www.sqlbi.com/articles/optimizing-dax-expressions-involving-multiple-measures/

Regards,

[v-cherch-msft]

Hi @Amitkr174 

Please refer to this article.It seems you may check if the below summarized rows are correct for your scenario.If you need further help,sample data or some assumed data which could reproduce your scenario will be helpful to provide an accurate solution.You can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.

Measure =
SUMX (
    SUMMARIZE (
        Budget,
        Project[Project Name],
        Budget[Budget Name],
        Budget[Budget ID]
    ),
    DIVIDE (
        [Est Vs Actuals Actual Billable Amount USD Std Total],
        [New Pacing% for a WK]
    )
)

Regards,