Forum Discussion

H_insight's avatar
H_insight
Icon for Helper V rankHelper V
3 years ago

Calculate Subtotal From a Template - Finance

Hiya,   I am using a custom template (Disconnected table) inside power bi to help me deliver a business layout.   Template:   I have created the following measures: Base measure:  Total...
"Need Help"
@NeedHelp
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

H_insight First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

H_insight's avatar
H_insight
Icon for Helper V rankHelper V
3 years ago

Thanks Greg_Deckler .

It's remarkable to realize that this concept was initially proposed back in July 2018, and its 5 YEARS anniversary is fast approaching without any resolution in sight!


I see your approach of having "No Calculate", but for some reason, I am not able to apply it to my disconnected table. any chance you can provide the DAX code for me to explorer, please?

 

So far, here is my attempt:

m_Total 4 =
VAR __table = SUMMARIZE('FACT','FACT'[SK], 'FACT'[Date], 'FACT'[Plan],"__value",[Total Amount])
RETURN
IF(HASONEVALUE(Template[SK]),[Total Amount],SUMX(__table,[__value]))
but I am getting the same values everywhere, which is normal as there is no relationship between 'Template' and 'FACT' tables.