Forum Discussion
Division with condition
I need a dax formulas that bring the % of each day on a month. The condition is: Same type of product. Not all products together. I usually get this with a simple division:
[product type sales of the day] / [Sum product type sales of the month]
I tried to use this but without success:
%ByDay:=DIVIDE(SUM(table[Value];CALCULATE(SUM(table[Value];ALLEXCEPT(table;table[product type])))
I use only one table, ie there are no relationships with other tables.
Maybe the problem is because there is more than one year/one month, idk.
Expected result
Example 1:
Example 2:
Any help must be apreciated. Blessings!
Hi Paulompm,
You can try to use below formula:
Current of Total= Divide(SUM(Table[Value]),SUMX(All(Table),[Value]),0) Current of Total(Same Type)= if(COUNTROWS(Table)<>COUNTROWS(ALL(Table)), Divide(SUM(Table[Value]),SUMX(FILTER(ALL(Table),Table[Product Type]=max(Table[Product Type])),[Value]),0),1)
Regards,
Xiaoxin Sheng
3 Replies
Hi Paulompm,
You can try to use below formula:
Current of Total= Divide(SUM(Table[Value]),SUMX(All(Table),[Value]),0) Current of Total(Same Type)= if(COUNTROWS(Table)<>COUNTROWS(ALL(Table)), Divide(SUM(Table[Value]),SUMX(FILTER(ALL(Table),Table[Product Type]=max(Table[Product Type])),[Value]),0),1)
Regards,
Xiaoxin Sheng
Man, what you have feels so correct though!?
%ByDay:=DIVIDE(SUM(table[Value];CALCULATE(SUM(table[Value];ALLEXCEPT(table;table[product type])))
Maybe just split it out to help debugging?
Total Value := SUM(table[Value])
Total Value - All In Type := CALCULATE([Total Value], ALLEXCEPT('Table', 'Table'[Product Type]))
% in Type := DIVIDE([Total Value], [Total Value - All In Type])There is no reason why this should change the result, but just to blow our minds... maybe try:
Total Value - All In Type := CALCULATE([Total Value], ALL('Table'), VALUES('Table'[Product Type]))