Forum Discussion
Solved
Formula Help
| Month | Country | Site | Program Name | HC |
| Jan-18 | US | Greenville | Running | 464 |
| Jan-18 | US | Greenville | Running | 464 |
| Jan-18 | US | Greenville | Running | 464 |
| Jan-18 | US | Greenville | Running | 464 |
| Jan-18 | US | Greenville | Running | 464 |
| Jan-18 | US | Greenville | Running | 464 |
| Jan-18 | US | Daleville | Running | 696 |
| Jan-18 | US | Daleville | Running | 696 |
| Jan-18 | US | Daleville | Running | 696 |
| Feb-18 | US | Greenville | Running | 424 |
| Feb-18 | US | Greenville | Running | 424 |
| Feb-18 | US | Greenville | Running | 424 |
| Feb-18 | US | Daleville | Running | 424 |
| Feb-18 | US | Daleville | Running | 424 |
| Feb-18 | US | Daleville | Running | 424 |
| Feb-18 | US | Greenville | Running | 424 |
Based on the above data i need a formula for ma power bi report in which in the table for for Jan i need the HC for US. As we can see that the program name is repeating. So i need to calcculate the HC in such a way that the formula should calulate the HC once even if the site and program name is repeating. The result should be as follows.
| Month | country | HC |
| Jan-18 | US | 1160 |
| Feb-18 | US | 824 |
Please try this MEASURE
MeasureF = VAR temp = CALCULATETABLE ( DISTINCT ( Table1[HC] ), ALL ( Table1[Mode] ) ) RETURN SUMX ( temp, [HC] )
12 Replies
HI unnijoy
Try this MEASURE
Measure = SUMX ( VALUES ( Table1[HC] ), CALCULATE ( VALUES ( Table1[HC] ) ) )
Hi Thanks for the Prompt reply. A small change in the Table.
Month Country Site Program Name Mode HC Jan-18 US Greenville Running Online 464 Jan-18 US Greenville Running ILT 464 Jan-18 US Greenville Running Online 464 Jan-18 US Greenville Running ILT 464 Jan-18 US Greenville Running Online 464 Jan-18 US Greenville Running ILT 464 Jan-18 US Daleville Running Online 696 Jan-18 US Daleville Running ILT 696 Jan-18 US Daleville Running Online 696 Feb-18 US Greenville Running ILT 424 Feb-18 US Greenville Running Online 424 Feb-18 US Greenville Running ILT 424 Feb-18 US Daleville Running Online 400 Feb-18 US Daleville Running ILT 400 Feb-18 US Daleville Running Online 400 Feb-18 US Greenville Running ILT 424 In the Table we can see Mode. So the HC should be calculated irrespective of Mode.
By the formula that u gave the system will calculate the HC seperately for each mode. our Formula should not be based on mode.
