wrong totals - Either get Grand Total on filtered values, or a super high value to blank()

[aldavies]

I have a DAX Measure issue I seem not to be able to resolve. I have watched many YouTube videos on the topic but it still did not work and I have referenced all my books on the subject, still nothing works

I am talking about how to fix wrong totals. Unfortunately, I cannot share with you the PBIX file as it is a Direct Query to my companies SSAS Tabular model.

I have a number of branching measures.

Starting with

All Category Spend % =
Divide ( [Total Spend], [All Category Spend] )

All Category Spend % =
IF(
HASONEVALUE('Supplier Tree'[Supplier ERP Name]),
DIVIDE([Total Spend (GC)], [All Category Spend]),
DIVIDE(
SUMX(
VALUES(
'Supplier Tree'[Supplier ERP Name]),
[Total Spend (GC)]),
SUMX(ALLSELECTED('Supplier Tree'[Supplier ERP Name]),
[Total Spend (GC)])))

Dominant Category =
IF(All Cate

gory Spend %] > 0.3, 1)

This is the final measure to give the total spend value for the row context that does not work.

Dominant Spend =
IF ( [Dominant Category] = 1, [Total Spend])


I have tried this

Dominant Category Spend =
SUMX( FILTER ( Spend, [Dominant Category] = 1), [Total Spend] )

AND I have tried

Dominant Category Spend =
IF(
HASONEVALUE(
CategoryTree[Category]),
IF([Dominant Category_Spend] =1, [Total Spend (GC)]),
SUMX(
CategoryTree,
IF([Dominant Category_Spend] = 1,[Total Spend (GC)])))

Still does not work as the row context at row Total for column Dominant Category is 1 or null and I think this is the issue why my totals for Dominant Spend is always the total for all rows. I have tried HASONEVALUE for the Dominant Category measure and this removes the total and then the Dominant Spend total is also blank.

Can you help me?

Thank you

Alan Davies

email: al.davies@dhl.com

[v-yuta-msft]

Hi aldavies,

Could you share some sample data(dummy data) and the description of relationship?

Regards,

Jimmy Tao

[aldavies]

Hi Jummy,

I have been working on this issue now for 2 days and still have not made any progress.   Here is the details and the new measure I have tried.

Spend is the FACT table  (Contains 66.5 million rows)
       Spend[Category_id]

       Spend[Supplier_id]

       Spend[Spend]

CategoryTree is a Filter Table  (Contains 346 rows)

        CategoryTree[Category_id]
        CategoryTree[Cluster]

        CategoryTree[Category]

        CategoryTree[SubCategory]

        CategoryTree[Commidity]

SupplierTree is a Filter Table  (Contains 1M rows)

        SupplierTree[Supplier_id]

        SupplierTree[Supplier Code]
        SupplierTree[Supplier Name]
        SupplierTree[Grouped Supplier Name]

Filter Context

Visual Filter = Top 100 Supplier Name by SUM(Spend[Spend])

Table in Power BI consists of

SupplierTree[Supplier Name]
CategoryTree[Category]
Total Spend (GC) = ( SUM(Spend[Spend]) )

X_Dominant Spend % =

    IF([Total Spend (GC)] = 0,BLANK(),
    DIVIDE([Total Spend (GC)],[All Supplier Spend]))

                    All Supplier Spend =
                    IF([Total Spend (GC)] =0, BLANK(),
                    CALCULATE(
                    [Total Spend (GC)],
                    ALLSELECTED(CategoryTree[Category])))

x_Dominant_Category =
        IF(HASONEVALUE(CategoryTree[Category]),
        IF([X_Dominant Spend %] >=0.3,[Total Spend (GC)],BLANK()),
        SUMX(
        FILTER('Spend',[X_Dominant Spend %] >=0.3),
        [Total Spend (GC)]))

For this measure, FILTER('Spend' - I have tried SupplierTree and CategoryTree, made no difference.

I have tried all examples I found on YouTube from BI Elite, Enterprise DNA, Curbal, and from the examples from this forum.
This is a screenshot of the table

I really hope you can help Jimmy.  

Thank you.

Alan Davies

[aldavies]

Hi Jimmy

How do I attached the sample data to this post?

---

@aldavies wrote:

Hi Jummy,

I have been working on this issue now for 2 days and still have not made any progress.   Here is the details and the new measure I have tried.

Spend is the FACT table  (Contains 66.5 million rows)
       Spend[Category_id]

       Spend[Supplier_id]

       Spend[Spend]

CategoryTree is a Filter Table  (Contains 346 rows)

        CategoryTree[Category_id]
        CategoryTree[Cluster]

        CategoryTree[Category]

        CategoryTree[SubCategory]

        CategoryTree[Commidity]

SupplierTree is a Filter Table  (Contains 1M rows)

        SupplierTree[Supplier_id]

        SupplierTree[Supplier Code]
        SupplierTree[Supplier Name]
        SupplierTree[Grouped Supplier Name]

Filter Context

Visual Filter = Top 100 Supplier Name by SUM(Spend[Spend])

Table in Power BI consists of

SupplierTree[Supplier Name]
CategoryTree[Category]
Total Spend (GC) = ( SUM(Spend[Spend]) )

X_Dominant Spend % =

    IF([Total Spend (GC)] = 0,BLANK(),
    DIVIDE([Total Spend (GC)],[All Supplier Spend]))

                    All Supplier Spend =
                    IF([Total Spend (GC)] =0, BLANK(),
                    CALCULATE(
                    [Total Spend (GC)],
                    ALLSELECTED(CategoryTree[Category])))

x_Dominant_Category =
        IF(HASONEVALUE(CategoryTree[Category]),
        IF([X_Dominant Spend %] >=0.3,[Total Spend (GC)],BLANK()),
        SUMX(
        FILTER('Spend',[X_Dominant Spend %] >=0.3),
        [Total Spend (GC)]))

For this measure, FILTER('Spend' - I have tried SupplierTree and CategoryTree, made no difference.

I have tried all examples I found on YouTube from BI Elite, Enterprise DNA, Curbal, and from the examples from this forum.
This is a screenshot of the table

I really hope you can help Jimmy.  

Thank you.

Alan Davies

---

---

@aldavies wrote:

Hi Jummy,

I have been working on this issue now for 2 days and still have not made any progress.   Here is the details and the new measure I have tried.

Spend is the FACT table  (Contains 66.5 million rows)
       Spend[Category_id]

       Spend[Supplier_id]

       Spend[Spend]

CategoryTree is a Filter Table  (Contains 346 rows)

        CategoryTree[Category_id]
        CategoryTree[Cluster]

        CategoryTree[Category]

        CategoryTree[SubCategory]

        CategoryTree[Commidity]

SupplierTree is a Filter Table  (Contains 1M rows)

        SupplierTree[Supplier_id]

        SupplierTree[Supplier Code]
        SupplierTree[Supplier Name]
        SupplierTree[Grouped Supplier Name]

Filter Context

Visual Filter = Top 100 Supplier Name by SUM(Spend[Spend])

Table in Power BI consists of

SupplierTree[Supplier Name]
CategoryTree[Category]
Total Spend (GC) = ( SUM(Spend[Spend]) )

X_Dominant Spend % =

    IF([Total Spend (GC)] = 0,BLANK(),
    DIVIDE([Total Spend (GC)],[All Supplier Spend]))

                    All Supplier Spend =
                    IF([Total Spend (GC)] =0, BLANK(),
                    CALCULATE(
                    [Total Spend (GC)],
                    ALLSELECTED(CategoryTree[Category])))

x_Dominant_Category =
        IF(HASONEVALUE(CategoryTree[Category]),
        IF([X_Dominant Spend %] >=0.3,[Total Spend (GC)],BLANK()),
        SUMX(
        FILTER('Spend',[X_Dominant Spend %] >=0.3),
        [Total Spend (GC)]))

For this measure, FILTER('Spend' - I have tried SupplierTree and CategoryTree, made no difference.

I have tried all examples I found on YouTube from BI Elite, Enterprise DNA, Curbal, and from the examples from this forum.
This is a screenshot of the table

I really hope you can help Jimmy.  

Thank you.

Alan Davies

---

[aldavies]

No, I have not yet received any solution so far.