Divide total using measures only

[airwolf39]

Hi all,

link to model: https://onedrive.live.com/?cid=9B9FE0DA1F0E085D&id=9B9FE0DA1F0E085D%213069&parId=root&o=OneUp

I have spent hours reading on this topic. I am very close, but cannot seem to get over the hump:

What i want to be able to do is divide 'Filtered Numerator' by 'Denomiator' at the aggregated level for the total. My row data is good. Did i use 'Summarize' incorrectly?

Also, all my measures are SUMX formulas. For example:

Thanks for you help guys.

[Anonymous]

HI @airwolf39.

You can take a look at the following links to know how to use 'if statement' to check current row content level and write an expression to handle total level calculations:

Clever Hierarchy Handling in DAX 

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[airwolf39]

Hi @Anonymous , 

That is what i have been trying to do. Use a IF(Hasonefilter) to write a secondary formula for the total row. No luck.

[Anonymous]

HI @airwolf39,

Can please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[airwolf39]

Its linked in the post. Thanks.

[Anonymous]

Hi @airwolf39,

In fact, I can't get sample data from the link you provided and it told me 'item missed'. Did this mean the file expired or you cancel the sharing?
Regards,

Xiaoxin Sheng

[amitchandak]

@airwolf39 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[airwolf39]

Here is the link:https://onedrive.live.com/?cid=9B9FE0DA1F0E085D&id=9B9FE0DA1F0E085D%213069&parId=root&o=OneUp

What im trying to do for 'Capable fill rate %':

• Numerator is [Todays Inventory on hand] + [WH4- week1]
• Denomiator is [SO Week -1] + [OverDue SO]
• Remove any negative values from the numerator (Today's inventory on hand). Those are system errors. But i want to keep them in the visual to highlight in meetings.
• Have the capable fill rate in the total row be correct
• The numerator cannot exceed the denominator, otherwise the capable fill rate would be over 100%. I used the min function to keep that from happening via a variable. 
• I would prefer to build this all in measures and not calculated columns.

[mahoneypat]

Using variables like that in your SUMMARIZE is incorrect.  If you provide a link to your pbix (or a mock up of it), a specific solution can be provided.  In any case, I suspect you are looking for a measure like this one.

NewMeasure =
SUMX (
    VALUES ( 'David Production Planning Report'[FG-Parents INV] ),
    VAR denominator = [SO - Week 1] + [Overdue SO]
    VAR denominator =
        MIN (
            [Today's Inventory] + [WH4 - Week 1],
            denominator
        ) + 0
    RETURN
        DIVIDE (
            numerator,
            denominator
        )
)

If you have only the Parents INV column and the above measure in your table visual, this should give you the correct values on both rows and in the total.

Regards,

Pat

[airwolf39]

That ended up not working for me. The row data is fine, but not the total row:

[airwolf39]

bump

[AllisonKennedy]

@airwolf39  Are you creating a new measure or a new column? What else do you have in your table in the screenshot if a measure? 

When you use variables, they are calculated when you define them and stored as a number, not as a formula. So if you want this calculated in the summarize, you may need to define them inside the SUMMARIZE function.

I'm not clear what you're trying to do, so if you can give more info we can help better.