Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
Please try this MEASURE
MeasureF = VAR temp = CALCULATETABLE ( DISTINCT ( Table1[HC] ), ALL ( Table1[Mode] ) ) RETURN SUMX ( temp, [HC] )
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.
Hi @unnijoy
I get correct results with the revised table even
Please see attached file
In that output table we have to include Mode also. We have once for field come in this table. For that we got the formula. Can you please help me to get the data in the below format.
Month | country | Mode | HC |
Jan-18 | US | ILT | 1160 |
Jan-18 | US | Online | 1160 |
Feb-18 | US | ILT | 824 |
Feb-18 | US | Online | 824 |
Even if i include the Mode Field in the Table.. I am getting the required output
I am getting it seperately. Like for December HC filters based on Online and ILT. In the top I have 4 slicers. Month, country, Program name,Mode. Is the mismatch because of this.
Please try this MEASURE
MeasureF = VAR temp = CALCULATETABLE ( DISTINCT ( Table1[HC] ), ALL ( Table1[Mode] ) ) RETURN SUMX ( temp, [HC] )
Month | Region | Country | Site | Program Name | Delivery Type | HC |
Jan-18 | North America | US | Rochester | - | ILT | 16 |
Jan-18 | North America | Canada | WHT | - | ILT | 205 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | Rochester | - | ILT | 16 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | Rochester | - | ILT | 16 |
Jan-18 | North America | US | Morrisville | - | ILT | 4 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | Greenville | - | ILT | 464 |
Jan-18 | North America | US | ST. LOUIS | G2 | ILT | 20 |
Jan-18 | North America | US | Greenville | C45 | ILT | 464 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Feb-18 | North America | US | WHT | G2 | ILT | 2053 |
Feb-18 | North America | US | WHT | G2 | ILT | 2053 |
Feb-18 | North America | US | WHT | - | ILT | 2053 |
Jan-18 | North America | US | Greenville | - | ILT | 464 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | ST. LOUIS | G2 | ILT | 20 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | Greenville | - | ILT | 464 |
Month | Region | Country | Site | Program Name | Delivery Type | HC |
Jan-18 | North America | US | Rochester | - | ILT | 16 |
Jan-18 | North America | Canada | WHT | - | ILT | 205 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | Rochester | - | ILT | 16 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | Rochester | - | ILT | 16 |
Jan-18 | North America | US | Morrisville | - | ILT | 4 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | Greenville | - | ILT | 464 |
Jan-18 | North America | US | ST. LOUIS | G2 | ILT | 20 |
Jan-18 | North America | US | Greenville | C45 | ILT | 464 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Feb-18 | North America | US | WHT | G2 | ILT | 2053 |
Feb-18 | North America | US | WHT | G2 | ILT | 2053 |
Feb-18 | North America | US | WHT | - | ILT | 2053 |
Jan-18 | North America | US | Greenville | - | ILT | 464 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | ST. LOUIS | G2 | ILT | 20 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | WHT | - | ILT | 2426 |
Jan-18 | North America | US | Greenville | - | ILT | 464 |
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |